Agh...getting error when referring to two tables

[dcnguyen]

OK, I thought updating a table based on values from another would be as easy as this:

UPDATE table1 SET table1.apples = table2.apples WHERE table1.oranges = table2.oranges

Those columns definitely exist. But I keep getting this error:

#1054 - Unknown column 'table2.oranges' in 'where clause'


Is there some archaic error I'm making?

 

[elsenorjose]

Try this:

UPDATE table1 SET table1.apples = (select table2.apples from table2) WHERE table1.oranges = table2.oranges

 

[dcnguyen]

Thanks...that took out the error message, but it returns 0 rows as affected, even though I know for sure there are matching entries.

 

[rac2]

Maybe the UPDATE aTable ... FROM aTable JOIN bTable syntax will work.

UPDATE table1 SET table1.apples = table2.apples
FROM table1
JOIN table2 ON table1.oranges = table2.oranges

 

[feherke]

Hi

Are you sure ? The [tt]from[/tt] clause is PostgreSQL [tt]update[/tt] extension. Was it added to MySQL [tt]update[/tt] too and I miss it ?


Feherke.

http://rootshell.be/~feherke/

 

[rac2]

UPDATE table1
       JOIN table2 ON table1.oranges = table2.oranges
SET table1.apples = table2.apples

I suppose if Feherke had known how to type this statement in MySQL syntax, he would have done so.

http://dev.mysql.com/doc/refman/5.0/en/update.html

 

[feherke]

Hi

I understand now. So that was just pseudo-code, not real statement.

Feherke.

http://rootshell.be/~feherke/

 

[dcnguyen]

oohhhhh...ok, I was wondering about the error message I got. I just gave up. Thanks for the help all.