CODEWITHSUNDEEP
sqlinformix
utkarsh tyagi
utkarsh tyagi

Reputation: 655

informix update query with joins

i am tryng to update one field of table using the values present in the other table, but it is giving "201: A syntax error has occurred." 

UPDATE  altr_destination
SET     ad.store_num = sx.new_store_num
FROM    altr_destination ad ,   store_xref sx
WHERE   ad.store_num = sx.old_store_num 
AND     ad.store_num = 9999 ;

Thanks, Utkarsh

Upvotes: 1

Views: 2287

Answers (1)

sagi
sagi

Reputation: 40481

Don't use implicit join syntax(comma separated) , use the proper syntax of joins!

It can be done with a correlated sub query as well :

UPDATE  altr_destination ad
SET     ad.store_num =(SELECT sx.new_store_num
                       FROM store_xref sx
                       WHERE   ad.store_num = sx.old_store_num)
WHERE ad.store_num = 9999 ;

I think update with join doesn't work on older versions of informix , at least that's what I saw when I looked for it.

Upvotes: 2

Related Questions