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
