Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to write update query for this?

 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you can try:
UPDATE schoolTransfering SET fromSchoolId = (Select school.id from school WHERE schooltransfering.fromSchoolName = school.name),toSchoolId =fromSchoolId;
 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Wei Dai,

But toSchool and fromSchoolId are not same, observing below,

 
Rob Spoor
Sheriff
Pie
Posts: 20669
65
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use two different aliases when combining the tables. The following at least works in MS SQL:
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic