CODEWITHSUNDEEP
sqlinformix
Kumar
Kumar

Reputation: 67

Update using Inner Join throwing syntax error

I would like to update columns in Table A based on values in Table B. Using below format, but getting syntax error.

update TableA
set 
TableA.MOdule_id =TableB.MOdule_id 
from TableA
inner join 
TableB
on TableA.end_Slot_id =TableB.Slot_Id 
where TableA.Slot_Id = 'AAA';

It would be great help, if anyone can help on this.

Upvotes: 1

Views: 118

Answers (1)

James K. Lowden
James K. Lowden

Reputation: 7837

A quick search for "informix sql update" returns two reference manual pages that describe the syntax for the UPDATE command. Neither one indicates support for the nonstandard FROM clause.

Standard SQL uses a correlated subquery for the purpose. Your query should look something like

update TableA
set MOdule_id =
    (select TableB.MOdule_id
     from TableB
     on TableA.end_Slot_id = Slot_Id)
where Slot_Id = 'AAA'
and exists (
     select 1
     from TableB
     on TableA.end_Slot_id = Slot_Id
     and TableA.Slot_Id = 'AAA'
);

The EXISTS clause ensures that only rows that exist in B are applied to A. Without it, any missing rows would be updated to NULL.

Upvotes: 3

Related Questions