This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to write update query for this? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to write update query for this?" Watch "How to write update query for this?" New topic
Author

How to write update query for this?

Nakataa Kokuyo
Ranch Hand

Joined: Jul 24, 2011
Posts: 184
Good day,

Assume i have a lookup table School like following:

Table School structure:



and following table SchoolTransferring with following structure ([] - empty/no value):


I need to update both fromSchoolId and toSchoolId at the same time where the id coming from lookup table School, but do i have chance to do it in once from single update query instead of calling another query to do so?

Below is my try in mySQL :

Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
Maybe you can try:
UPDATE schoolTransfering SET fromSchoolId = (Select school.id from school WHERE schooltransfering.fromSchoolName = school.name),toSchoolId =fromSchoolId;
Nakataa Kokuyo
Ranch Hand

Joined: Jul 24, 2011
Posts: 184
Hey Wei Dai,

But toSchool and fromSchoolId are not same, observing below,

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19649
    
  18

Use two different aliases when combining the tables. The following at least works in MS SQL:


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to write update query for this?
 
Similar Threads
Update Table Nested Query (Using Function)
entity loading blues
Cannot perform SQL UPDATE null
Get Updated Record
batch update in SQL