aspose file tools*
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 Spring in Action this week in the Spring 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: 188
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: 188
Hey Wei Dai,

But toSchool and fromSchoolId are not same, observing below,

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19722
    
  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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to write update query for this?