Refresh Informix table with joins

Question

Is this the correct syntax for updating Informix?

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 a generic -201 "Syntax error" error message, but I don’t see what is wrong.

Answer 1

your syntax error is table1.code

 set table1.code = 100 

change it to

 set a.code = 100 

Full code

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

Answer 2

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

This is the only way to avoid a 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, get the Informix version , run the following SQL:

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

Answer 3

Source SQL in question:

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

This unconditionally missing series of I-words. The decision also identifies the problem in the SET clause using table1 instead of its alias a . It may be material; I cannot verify it (see discussion below). So, assuming the UPDATE join is accepted at all, the corrected SQL should read:

 UPDATE table1 SET a.code = 100 FROM table1 a, table2 b, table3 c WHERE a.key = c.key AND a.no = b.no AND a.key = c.key AND a.code = 10 AND b.tor = 'THE' AND a.group = 4183 AND a.no IN ('1111','1331','1345') 

This is the same as the accepted (syntactically corrected) answer. However, I am curious to know which version of Informix you are using that accepts FROM syntax (maybe XPS?). I am using IDS 11.70.FC2 (3 fix packs for the current version 11.70.FC5) on Mac OS X 10.7.4, and I cannot get UPDATE with FROM syntax to work. In addition, the IBM Informix 11.70 Information Center for UPDATE manual does not mention this. I am not sure if it will matter if you use ODBC or JDBC; it should not, but I am using ESQL / C, which sends SQL unchanged to the server.

The designation I tried ( + is an invitation):

 + BEGIN; + CREATE TABLE a(a INTEGER NOT NULL, x CHAR(10) NOT NULL, y DATE NOT NULL); + INSERT INTO a(a, x, y) VALUES(1, 'obsoletely', '2012-04-01'); + INSERT INTO a(a, x, y) VALUES(2, 'absolutely', '2012-06-01'); + CREATE TABLE b(b INTEGER NOT NULL, p CHAR(10) NOT NULL, q DATE NOT NULL); + INSERT INTO b(b, p, q) VALUES(3, 'daemonic', '2012-07-01'); + SELECT * FROM a; 1|obsoletely|2012-04-01 2|absolutely|2012-06-01 + SELECT * FROM b; 3|daemonic|2012-07-01 + SELECT * FROM a, b WHERE aa < bb AND bp MATCHES '*a*e*'; 1|obsoletely|2012-04-01|3|daemonic|2012-07-01 2|absolutely|2012-06-01|3|daemonic|2012-07-01 + UPDATE a SET x = 'crumpet' FROM a, b WHERE aa < bb AND bp MATCHES '*a*e*'; SQL -201: A syntax error has occurred. SQLSTATE: 42000 at <<temp>>:23 + SELECT * FROM a; 1|obsoletely|2012-04-01 2|absolutely|2012-06-01 + ROLLBACK; 

Answer 4

For Informix SE 7.25 ...

• UPDATE ... FROM ... syntax does not exist
• You also can’t “Modify the table or view used in the subquery” that is provided when using the Rockallite answer

Another solution would be to split it into two queries:

First get the ROWID for the required records (filtered by multiple tables):

 SELECT a.ROWID FROM table1 a, table2 b, table3 c WHERE a.key = c.key AND a.no = b.no AND a.key = c.key AND a.code = 10 AND b.tor = 'THE' AND a.group = 4183 AND a.no IN ('1111','1331','1345') 

Put the result in a comma separated line.

Then update only those records for the main table where the ROWID was found in the first query:

 UPDATE table1 a SET a.code = 100 WHERE a.ROWID in ([comma separated ROWIDs found above]) 

Answer 5

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 has been 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 tb = t2.b;