MySQL Update – How to Perform an UPDATE from a SELECT

MySQLselectupdate

The more I learn about about the great things you can do with mysql, the more I keep pushing to learn.

I have two tables:

Tests

TestNumber (int primary key)
InactiveTestSlotBitwise (int)

TestUsers

UserId (int)
TestNumber (int - ties in with the TestNumber in Tests)
UserSlot (int the person's seating position in the test)

I had been doing the following in two stages in php, but now see that as cumbersome. I was collecting all the 'TestUsers' with the UserId = 25 and returning to the php code which then does a separate call the database and then alters the 'InactiveTestSlotBitwise' in the 'Tests' to show they had removed themselves from the Tests they were in.

However, now I think I should be doing something else instead in one call, on the lines:

UPDATE tests AS t SET
    t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
FROM
    (SELECT TestNumber, UserSlot FROM testusers
        WHERE UserId=25 AND UserSlot >= 0
    ) AS tu
    WHERE t.TestNumber= tu.TestNumber

Surely this is possible? It does not like it but does really say why. It says:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT TestNumber, UserSlot …'

Best Answer

MySQL doesn't have syntax for UPDATE ... FROM, however it does allow for UPDATE table1, table2,... SET table1.col = value WHERE table1.id = table2.id. You can try the following:

UPDATE Tests AS t, 
       (SELECT TestNumber, UserSlot FROM TestUsers
        WHERE UserId=25 AND UserSlot >= 0) AS tu
SET
    t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
WHERE t.TestNumber= tu.TestNumber;
Related Question