Use WHERE NOT EXISTS in same table with composite PK in Informix

Question

I have one table with a composite of 5 fields in an Informix database and I need to duplicate some values and just change the rm_id field. That part is already working. The problem comes when trying to duplicate it and one row with these 5 values already exists and I received a Unique constraint violated error. I started to write a query for this and I did this:

SELECT *
FROM table t1
WHERE t1.rm_id = 249
AND NOT EXISTS (SELECT t2.prop, t2.rb_code, t2.bm_id, t2.bl, t2.rm_id
FROM table t2
WHERE t2.prop = t1.prop, t2.rb_code = t1.rb_code, t2.bm_id = t1.bm_id, t2.bl = t1.bl, t2.rm_id = t1.rm_id);

I received a syntax error but I can't find any problem with my query; is there any way to do something like this in Informix?

see possible duplicate https://stackoverflow.com/questions/4596390/insert-on-duplicate-key-do-nothing/4596409 — Scary Wombat, Nov 19 '21 at 06:44

Jonathan Leffler · Accepted Answer · 2021-11-19T15:58:06.437

Use AND instead of commas to separate the conditions in the WHERE clause.

SELECT *
  FROM table t1
 WHERE t1.rm_id = 249
   AND NOT EXISTS (SELECT t2.prop, t2.rb_code, t2.bm_id, t2.bl, t2.rm_id
                     FROM table t2
                    WHERE t2.prop = t1.prop
                      AND t2.rb_code = t1.rb_code
                      AND t2.bm_id = t1.bm_id
                      AND t2.bl = t1.bl
                      AND t2.rm_id = t1.rm_i
                  );

Also, for a [NOT] EXISTS operation, it doesn't really matter what you list as the select-list items. It is conventional to list NOT EXISTS(SELECT * FROM …).

Use WHERE NOT EXISTS in same table with composite PK in Informix

1 Answers1