Anh Bui wrote:
> 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.
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 (
Your above syntax is illegal in SQL92.
But some RDBMS have it (or something similar).