MySQL Update Query issue

Question

I know it has something to do with the syntax, but I'm looking for a way to do the following:

UPDATE modules
SET ServerID = boards.ServerID
FROM boards,modules
WHERE modules.ID = boards.ID

this doesnt work. I'm using MySQL

Answer 1

Try this:

UPDATE boards,modules
SET modules.ServerID = boards.ServerID
WHERE modules.ID = boards.ID

Read MYSQL UPDATE syntax at http://dev.mysql.com/doc/refman/5.0/en/update.html

You can also perform UPDATE operations covering multiple tables ....

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

The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.