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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 176
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: 176
Hey Wei Dai,

But toSchool and fromSchoolId are not same, observing below,

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19541
    
  16

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?
 
Similar Threads
entity loading blues
Update Table Nested Query (Using Function)
batch update in SQL
Cannot perform SQL UPDATE null
Get Updated Record