Informix SQL Update using an inner join
Informix SQL Update using an inner join
(OP)
I have a need to update 270,000 records with a particular flag. Using temporary tables and multiple update queries I have creating a staging table to put the original value and the new value. So the table structure is like this:
I'm struggling with creating an efficient UPDATE query that will update these records in mass.
In MS SQL I have used an INNER JOIN to accomplish this, but it seems that Informix doesn't support that. Right now I'm structuring the query like this:
Is there a better way to accomplish this?
Thank you for any guidance!
CODE --> Structure
ID ORIG_VALUE NEW_VALUE 1223 A B 1234 D A 1334 ...
In MS SQL I have used an INNER JOIN to accomplish this, but it seems that Informix doesn't support that. Right now I'm structuring the query like this:
CODE --> SQL
UPDATE TABLEA SET prod_field = ( SELECT new_field FROM TABLEB_STAGING WHERE TABLEA.id = TABLEB_STAGING.id)
Is there a better way to accomplish this?
Thank you for any guidance!