Need help running this MS SQL Server statement on Informix

I need help running t开发者_如何学Chis MS SQL Server UPDATE statement on Informix (version 11):

update b  
set Colname = 'StringValue'
from Table1 b right join Table1 c 
on ((b.Col1 = c.Col1) and (b.Col2 = c.Col2)) 
where ((b.Col3 = 'S' and b.Col4 <> 'S') and (c.Col3 = 'Z' and c.Col4 <> 'S'))

I keep getting error number -201 (syntax error).

Can you see any syntax error? Any ideas?

Which version of Informix are you using?

Actually, I don't think it matters...IDS does not support join notations in the UPDATE statement, even in the latest version. So, the problem is you are trying to use a notation that is not supported by the DBMS, and hence you get back the annoying (but, in this case, accurate) generic "-201: A syntax error has occurred". I don't think even IDS 11.70.xC1, the latest GA version, supports table aliases in the UPDATE statement, either (which complicates the query).

I confess that the RIGHT {self} JOIN has me bemused - I'm not sure I understand how it should work. However, here is a moderate approximation to the requested update:

UPDATE Table1
   SET Colname = 'StringValue'
 WHERE Table1.Col3 = 'S'
   AND Table1.Col4 <> 'S'
   AND EXISTS(SELECT * FROM Table1 AS C
               WHERE C.Col1 = Table1.Col1 AND C.Col2 = Table1.Col2
                 AND C.Col3 = 'Z'
                 AND C.Col4 <> 'S'
             )

The nagging doubts are two-fold:

• Will IDS disambiguate the references to Table1 in the EXISTS sub-query correctly?
• What does that RIGHT JOIN mean?

Unfortunately, when the query is run, I get back:

SQL -360: Cannot modify table or view used in subquery.

There are workarounds for that, using temporary tables, but they're a nuisance. However, this example code seems to work according to my expectations (given that I still can't wrap my brain around what the RIGHT JOIN is doing in the original).

CREATE TABLE table1
(
    col1    INTEGER NOT NULL,
    col2    INTEGER NOT NULL,
    col3    CHAR(1) NOT NULL,
    col4    CHAR(1) NOT NULL,
    colname VARCHAR(32) NOT NULL
);

-- The first row shown is updated - the others are unchanged
INSERT INTO table1 VALUES(1, 1, 'S', 'A', 'Old value'); 
INSERT INTO table1 VALUES(1, 1, 'Z', 'A', 'Old value'); 
INSERT INTO table1 VALUES(1, 2, 'S', 'A', 'Old value'); 
INSERT INTO table1 VALUES(1, 2, 'Z', 'S', 'Old value'); 
INSERT INTO table1 VALUES(1, 3, 'S', 'S', 'Old value'); 
INSERT INTO table1 VALUES(1, 3, 'Z', 'S', 'Old value'); 
INSERT INTO table1 VALUES(1, 4, 'S', 'S', 'Old value'); 
INSERT INTO table1 VALUES(1, 4, 'Z', 'A', 'Old value');     

SELECT * FROM Table1 WHERE Col3 = 'Z' AND Col4 <> 'S' INTO TEMP C;

UPDATE Table1
   SET Colname = 'StringValue'
 WHERE Table1.Col3 = 'S'
   AND Table1.Col4 <> 'S'
   AND EXISTS(SELECT * FROM {Table1 AS} C
               WHERE C.Col1 = Table1.Col1 AND C.Col2 = Table1.Col2
                 AND C.Col3 = 'Z'
                 AND C.Col4 <> 'S'
             );

The fragment '{Table1 AS}' is a comment in Informix. The conditions on Col3 and Col4 are not strictly necessary because of the way that temp table C is created.

The results I get from SELECT * FROM Table1 ORDER BY Col1, Col2, Col3, Col4 before and after the UPDATE statement are:

Before
1   1   S   A   Old value
1   1   Z   A   Old value
1   2   S   A   Old value
1   2   Z   S   Old value
1   3   S   S   Old value
1   3   Z   S   Old value
1   4   S   S   Old value
1   4   Z   A   Old value

After
1   1   S   A   StringValue
1   1   Z   A   Old value
1   2   S   A   Old value
1   2   Z   S   Old value
1   3   S   S   Old value
1   3   Z   S   Old value
1   4   S   S   Old value
1   4   Z   A   Old value