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:
TestNumber (int primary key) InactiveTestSlotBitwise (int)
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 ...'