List:MySQL and Java« Previous MessageNext Message »
From:Christian Mack Date:September 20 1999 6:36pm
Subject:Re: How to update a table using data from another table
View as plain text  
Anh Bui wrote:
> 
> Hi,
> 
> Does anyone know how to update a table using data from another table?  For example,
> if I had two tables with the following fields:
> Table One: Student Id, StudentStatus
> Table Two: Student Id, GradeAverage
> 
> How can I update StudentStatus in table one using the GradeAverage in table two?  I
> thought that the following SQL statement would work, but it does not:
> 
> UPDATE table1 SET StudentStatus = "Pass"  WHERE table1.StudentId=table2.StudentId and
> table2.GradeAverage >= 3.0
> 
> The error I get is: "ERROR 1109: Unknown table 'table2' in where clause".
> 
> Your help would be greatly appreciated.  Thank you.
> 
> Anh

Hi Anh

Sorry, but you can't do this till yet with mysql.
The only way is, to get all table2.StudentId's first, and then use:
UPDATE table1 SET StudentStatus = 'Pass' WHERE table1.StudentId IN (
list_you_got_in_the_query_before );

BTW:
Your above syntax is illegal in SQL92.
But some RDBMS have it (or something similar).

Tschau
Christian

Thread
How to update a table using data from another tableAnh Bui20 Sep
  • Re: How to update a table using data from another tableChristian Mack20 Sep