update sql query using joins informix

Question

I have one table table1 (id, name, surname, ssn) and a view1 (id, ssn) and here is my update clause

update table1 set 
ssn=v.ssn
from table1 t,view v 
where t.id=v.id

However I get syntax error sql code -201, does anybody knows what is the problem?

Answer 1

Can you try:

UPDATE table1 SET ssn=(SELECT ssn FROM view WHERE table1.id=view.id)

PS You use strange names: table1, view. They say nothing about data in those tables/views. I hope this is only for this question.

Answer 2

You can use the MERGE statement.
But this depends the version of the Informix engine are you working (needs version 11.50 for this answer work).

Check this other similar question/answer answer for more information.

MERGE INTO table1 as t1
USING table2 as t2
   ON t1.ID = t2.ID
WHEN MATCHED THEN UPDATE set (t1.col1, t1.col2) = (t2.col1, t2.col2);