Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question
In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at:
Maybe you can just join the table to itself
If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.
UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col
Alternatively, try nesting the subquery deeper into a from clause ...
If you absolutely need the subquery, there's a workaround, but it's
ugly for several reasons, including performance:
UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);
The nested subquery in the FROM clause creates an implicit temporary
table, so it doesn't count as the same table you're updating.
... but watch out for the query optimiser
However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the
optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.
SET optimizer_switch = 'derived_merge=off';
Thanks to Peter V. Mørch for this advice in the comments.
Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.
Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp
("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
Although the manual seems to suggest the INNER JOIN syntax should work in a DELETE, I know that this alternative with the join clause moved to the where condition would work....
Edit: I just tried this, which worked for me: