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

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?

CodePudding user response：

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 …).

CodePudding user response：

in simple i say that you are framing like select x from table1 where table1.id = y and not exists(table2.abc) from table2 where table2=d

that's the reason you are getting the error.

try to join table1 and table2 1st and apply conditions, this issue will get solve.