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
Add a comment
|
4 Answers
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.
1 Comment
Phil
You should probably prefix
ServerID with modules to avoid an ambiguous column referenceUPDATE modules
SET ServerID = (SELECT b.ServerID FROM boards b WHERE b.ID = modules.ID)
Comments
UPDATE modules
INNER JOIN boards
ON modules.ID = boards.ID
SET modules.ServerID = boards.ServerID
Comments
UPDATE syntax does not include FROM
FROM is meant for SELECT.
if you want to combine the two:
UPDATE modules SET ServerID = (SELECT boards.ServerID FROM boards WHERE boards.ID = modules.ID)
