Update with subquery

[Manish Hatwalne]

I am trying to update a related table with a subquery. This is what I am
using -
update table1 set col1=col1+1 where ID in (select ID from table2 where
status="Active');

But I get syntax error. Can someone help, I am lost!!!

TIA,
- Manish

[somkiat puisungnoen]

update table1 set col1=col1+1 where ID in (select ID from table2 where
status="Active');

MUST Chenge to

update table1 set col1=col1+1 where ID in (select ID from table2 where
status='Active');

---------------

and please post your message error for solve this problem.

[Manish Hatwalne]

I made a typo while posting here, the orginal query did have proper quotes. (I typed it by hand because field names, table names etc are generated dynamically by the code). The reason it was not working because MySQL supports subqueries only in versions 4.1 and up. The version I have is 4.0.14-nt.

- Manish

[Surasak Leenapongpanit]

Hi Manish,
How do you solve on your sql without subquery?

[Manish Hatwalne]

Originally posted by Surasak Leenapongpanit:
Hi Manish,
How do you solve on your sql without subquery?

UPDATE tbl1, tbl2 SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl1.ID = tbl2.ID AND tbl2.status='Active';

- Manish

[Gregg Bolinger]

I think this would be better off in the JDBC forum where all the SQL guys and gals hang out.

[Julian Kennedy]

Originally posted by Manish Hatwalne:

UPDATE tbl1, tbl2 SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl1.ID = tbl2.ID AND tbl2.status='Active';

A note for any interested readers: this syntax will only work in MySQL. I believe the ANSI standard still requires use of a sub-query, which can be rather cumbersome. Most RDBMS have their own shorthand solutions to this.

Jules