GeeCON Prague 2014*
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


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: 187
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: 187
Hey Wei Dai,

But toSchool and fromSchoolId are not same, observing below,

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

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
 
GeeCON Prague 2014
 
subject: How to write update query for this?