Update table from other table

[pookie62]

I'm trying to write an update query.
I want to read values from one table and insert those values in the second table.
I tried this

update 
klassement, deelnemerklasselevel 
SET 
klassement.LevelId = deelnemerklasselevel.levelId
where klassement.deelnemerId = deelnemerklasselevel.deelnemerId 
AND 
klassement.KlasseId = deelnemerklasselevel.KlasseId;

I get message 'Unknown column klassement.klasseId in where clause'

Can't figure it out.. you ?

[guelphdad]

Does your version of mysql support multi-table updating? 4.0.4 or higher IIRC.

[pookie62]

Hi daveman,
I'm not sure which version it is, but definitely not above 4.xx
Update is only ment for one table, getting values from another..

[r937]

pookie, just run this query to be sure --

select version()

yes, you are only updating one table, but unless you're on 4.0+ you cannot mention another table in the UPDATE statement

[pookie62]

Thanks rudy,

Version is 3.23.58.
So is there no way to do this ?

[r937]

you may need to write some application logic

the dreaded query in a loop, i think