aspose file tools*
The moose likes JDBC and the fly likes Update  with subquery Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update  with subquery" Watch "Update  with subquery" New topic
Author

Update with subquery

Manish Hatwalne
Ranch Hand

Joined: Sep 22, 2001
Posts: 2578

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

Joined: Jul 04, 2003
Posts: 1312
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.


SCJA,SCJP,SCWCD,SCBCD,SCEA I
Java Developer, Thailand
Manish Hatwalne
Ranch Hand

Joined: Sep 22, 2001
Posts: 2578

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

Joined: May 10, 2002
Posts: 341
Hi Manish,
How do you solve on your sql without subquery?
Manish Hatwalne
Ranch Hand

Joined: Sep 22, 2001
Posts: 2578

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

Joined: Jul 11, 2001
Posts: 15299
    
    6

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


GenRocket - Experts at Building Test Data
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update with subquery