wood burning stoves 2.0*
The moose likes JDBC and the fly likes Updating multiple tables in one sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating multiple tables in one sql query" Watch "Updating multiple tables in one sql query" New topic
Author

Updating multiple tables in one sql query

Luke Zechariah
Ranch Hand

Joined: Sep 27, 2005
Posts: 117
Hello,
I am writing a sql query where I want to update 2 tables in one query as this:

UPDATE table1 as t1, table2 as t2 SET t1.UpdateFlag = 'N', t2.UpdateFlag = 'N' where t1.IdNumber = t2.IdNumber and t1.UpdateFlag = 'T' and t2.UpdateFlag = 'T'

It is not working, please help how to proceed.

Thanks,
Luke.
saurav sarkar
Ranch Hand

Joined: Jan 07, 2007
Posts: 180

what is the error you get?


Be Objectively Oriented.Explore the power of OOPs.
My Blog, Eclipse EMF Query committer.
Luke Zechariah
Ranch Hand

Joined: Sep 27, 2005
Posts: 117
Hi Saurav,

Thanks for the reply, here is the error:

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'."

I am using MS query analyzer. This error does not help me much. Any suggestions.

Thanks,
Luke.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

As far as I am aware, you can't update two tables with one update statement. Someone might tell me I'm wrong here, but I think your only choices are two update statements or stored procedure.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Luke Zechariah
Ranch Hand

Joined: Sep 27, 2005
Posts: 117
Thanks Paul for the information.
Luke.
Nick Bruns
Greenhorn

Joined: Sep 16, 2008
Posts: 1
It can be done,

http://www.christonium.com/SearchEngineMarketing/ItemID=11936974368828

That's with MySQL though. But sorry I don't know what's wrong with your query...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Updating multiple tables in one sql query
 
Similar Threads
retrieve data from huge result set
Displaying resultset in many pages
Hibernate HQL and QBE questions
Help me optimize this query
Compare Two Column