0

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 ...'

1

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;
2

Try the following:

UPDATE tests AS t JOIN testusers AS tu ON t.TestNumber= tu.TestNumber 
SET t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot)) 
WHERE tu.UserId=25 AND tu.UserSlot >= 0;
  • Thanks for the answer. It is just what i was after. I think Ziggy must have slightly answered first so I have mark his with the tick. – Rewind Apr 20 '16 at 10:54
  • Note that if you are using MySQL 5.6 or older, Ziggy's proposal will require MySQL to use a temporary table due to the subquery used. Hence, my proposal should be more performant (if that matters). In MySQL 5.7, both proposals will use the same query plan since the MySQL optimizer is now able to merge such subqueries into the main query. – oysteing Apr 21 '16 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.