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.

Preterition

The syntax shown is missing a large number of AND keywords. – Ubald

Answer 1

your syntax error is table1.code

set table1.code = 100

change this into

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

Celiaceliac

hi, I have a simular update/join query but rather than setting the a.code value to static value of 100, i need to reference a column value on a join table. I've tried 'table3.key' and 'c.key' but with no luck. – Kape

Unfortunately, this causes syntax error in Informix 11.50. (To get Informix server version, run SQL select first 1 dbinfo("version", "full") from systables;) – Apposition

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.

Apposition

The same holds for Version 11.70 – Margarethe

The same holds for Version 11.10 – Anastasiaanastasie

Answer 3

The original SQL in the question was:

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 is unconditionally missing a series of AND keywords. The accepted solution also identifies a problem in the SET clause with the use of table1 instead of its alias a. That might be material; I can't test it (see discussion below). So, assuming that the join UPDATE 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 (syntax-corrected) accepted answer. However, I'm curious to know which version of Informix you are using that accepts the FROM syntax (maybe XPS?). I'm using IDS 11.70.FC2 (3 fix packs behind the current 11.70.FC5 version) on Mac OS X 10.7.4, and I can't get the UPDATE with FROM syntax to work. Further the manual at the IBM's Informix 11.70 Information Center for UPDATE does not mention it. I'm not sure whether it would make any difference if you're using ODBC or JDBC; it shouldn't, but I'm using ESQL/C, which sends the SQL unchanged to the server.

The notation I tried is (+ is the prompt):

+ 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 a.a < b.b
    AND b.p 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 a.a < b.b
    AND b.p 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;

Ubald

Found this page in the XPS docs while hunting for a solution, so it looks like your guess was right. publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/… – Douala

Regrettably, the link in @Frosty840's answer was 404 'not found' when checked on 2017-11-09. – Ubald

Answer 4

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;

Maharani

He was asking about INFORMIX – Kalif

Answer 5

For Informix SE 7.25...

1. UPDATE ... FROM ... syntax does not exist
2. You also "Cannot modify table or view used in subquery" which is given when using Rockallite's answer

Another solution would be to break it down into two queries:

First, get the ROWIDs for the required records (filtered on 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 into a comma separated string.

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

Interactive