MySQL UPDATE Syntax problem

[twilson]

I'm having a slight problem with a simple UPDATE command. This is the command I am using:

UPDATE movers,movers_backup SET movers.home_number=movers_backup.home_number WHERE movers.zipplusfour=movers_backup.zipplusfour;

This is the error I am getting:

ERROR 1064: You have an error in your SQL syntax near 'movers_backup SET movers.home_number=movers_backup.home_number WHERE movers.zipp' at line 1

This is the UPDATE syntax from the MySQL site:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

And the example shown on the MySQL site:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Now I've looked at this like 100 times and can see nothing wrong with it. Maybe I just need someone else to look at it. Some things you should know are that movers and movers_backup have the exact same structure and zipplusfour is unique.

Thanks in advance for any help you can offer,
Tom 😕

[xblue]

good idea to look at the example in the manual, but it says

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

3.x is still fairly common, are you sure you are running 4.0.4?

[twilson]

Maybe I should pay more attention. That's the problem, thanks.

Tom =)