Обсуждение: INSERT only under certain conditions (SELECT)
Hi, I want to insert a row only under condition that there isn't already another row with similar values -- something like a INSERT INTO ... WHERE NOT EXISTS (SELECT ...)? Hoping for help, joachim
create an uniq index on the columns, # create unique index index_name on table (col1,col2,col3,...) hope it helps. regds mallah On Monday 08 July 2002 06:48, Joachim Trinkwitz wrote: > Hi, > > I want to insert a row only under condition that there isn't already > another row with similar values -- something like a INSERT INTO > ... WHERE NOT EXISTS (SELECT ...)? > > Hoping for help, > joachim > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Mon, Jul 08, 2002 at 03:18:33 +0200, Joachim Trinkwitz <jtr@uni-bonn.de> wrote: > Hi, > > I want to insert a row only under condition that there isn't already > another row with similar values -- something like a INSERT INTO > ... WHERE NOT EXISTS (SELECT ...)? You can do something like the following: insert into tab (col1, col2, col3) select 'val1', 'val2', 'val3' where not exists (select * from tab where col1 = 'val1' and col2 = 'val2' and col3 = 'val3');
Maybe I should explain my problem a little bit: simplified, I have two tables, L and T, (which values references to L): L: art|kvvnr|semester---+-----+--------7.0| 4151| 2002ws7.0| 4326| 2002ws6.1| 4200| 2002ws7.0| 4151| 2001ws (combination of kvvnr and semester is unique) T: id |semester|kvvnr----+--------+----- 123| 2002ws |4151 123| 2001ws |4151 Now I want to allow a user with id 123 only to insert a row in T if he doesn't choose values from L with same 'art' and 'semester' values -- in the examples user 123 has already chosen a kvvnr from semester 2002ws in category 'art', so he shouldn't be allowed to insert the values of the second row in table L. I hope, someone has followed this complicated thing up to here and there is a solution for my problem. Greetings and thanks for your answers by now and further on, joachim
On Mon, Jul 08, 2002 at 17:49:56 +0200, Joachim Trinkwitz <jtr@uni-bonn.de> wrote: > > Now I want to allow a user with id 123 only to insert a row in T if he > doesn't choose values from L with same 'art' and 'semester' values -- > in the examples user 123 has already chosen a kvvnr from semester > 2002ws in category 'art', so he shouldn't be allowed to insert the > values of the second row in table L. In the database you enforce this using a unique index. The application needs to be able to handle failures when you try to insert a duplicate key. If you can't afford to have a transaction fail part way through, then anything that writes that table should do a table lock at the start of the transaction.