• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update with subquery

 
Manish Hatwalne
Ranch Hand
Posts: 2591
Android Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1312
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2591
Android Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Manish,
How do you solve on your sql without subquery?
 
Manish Hatwalne
Ranch Hand
Posts: 2591
Android Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think this would be better off in the JDBC forum where all the SQL guys and gals hang out.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic