update Informix table with joins

Question

Is this the correct syntax for an Informix update?

update table1
set table1.code = 100
from table1 a, table2 b, table3 c
where a.key = c.key
a.no = b.no
a.key = c.key
a.code = 10
b.tor = 'THE'
a.group = 4183
a.no in ('1111','1331','1345')

I get the generic -201 'A syntax error has occurred' message, but I can't see what's wrong.

Answer 1

It depends on the version you are using. If you are using at least 11.50 the best solution would be:

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);

The UPDATE - SET - FROM - Syntax was removed in versions greater than 11.50.

If you are using an earlier version you can go with

UPDATE t SET a = t2.a FROM t, t2 WHERE t.b = t2.b;

Answer 2

Unfortunately, the accepted answer causes syntax error in Informix Dynamic Server Version 11.50.

This is the only way to avoid syntax error:

update table1
set code = (
  select 100
  from table2 b, table3 c
  where table1.key = c.key
  and table1.no = b.no
  and table1.key = c.key
  and table1.code = 10
  and b.tor = 'THE'
  and table1.group = 4183
  and table1.no in ('1111','1331','1345')
)

BTW, to get Informix version, run the following SQL:

select first 1 dbinfo("version", "full") from systables;

Updated: also see this answer.

Updated: also see the docs.