CODEWITHSUNDEEP
mysqlsqldatabase
snoofkin
snoofkin

Reputation: 8895

MySQL Update Query issue

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

Upvotes: 3

Views: 125

Answers (4)

galchen
galchen

Reputation: 5290

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)

Upvotes: -1

Gajahlemu
Gajahlemu

Reputation: 1263

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.

Upvotes: 3

Phil
Phil

Reputation: 164776

UPDATE modules
INNER JOIN boards
ON modules.ID = boards.ID
SET modules.ServerID = boards.ServerID

Upvotes: 1

Cliff
Cliff

Reputation: 1701

UPDATE modules
SET ServerID = (SELECT b.ServerID FROM boards b WHERE b.ID = modules.ID)

Upvotes: 1

Related Questions