update query error

[dagoat10]

I am trying to update one column of a table from another column of another table, but i keep getting errors

here is what i have tried:
mysql> Update NFL_Players2
-> SET Number = Player2.Number
-> Where NFL_Players2.Number = Player2.Number;
ERROR 1109: Unknown table 'Player2' in where clause

Also i have tried this:
mysql> Update NFL_Players2 INNER JOIN Player2
-> ON Player2.Player = NFL_Players2.Player
-> SET NFL_Players2.Number = Player2.Number
-> WHERE Player2.Player = "Derek Anderson";
ERROR 1064: You have an error in your SQL syntax near 'INNER JOIN Player2
ON Player2.Player = NFL_Players2.Player
SET NFL_Players2.Numb' at line 1


All the necessary tables exist, so what is missing?

 

[r937]

what version of mysql are you running?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dagoat10]

mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686)

 

[r937]

3.23 is oooooooooold, and does not support JOIN syntax

try it like this --

UPDATE NFL_Players2 
     , Player2
   SET NFL_Players2.Number = Player2.Number
 WHERE Player2.Player = NFL_Players2.Player
   AND Player2.Player = 'Derek Anderson'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

oh, wait... scratch that

3.23 is ~too~ old

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables

looks like you are outta luck



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon