You can use the INSERT … SELECT statement to get the MAX()+1 value and insert at the same time: INSERT INTO customers( customer_id, firstname, surname ) SELECT MAX( customer_id ) + 1, ‘jim’, ‘sock’ FROM customers; Note: You need to drop the VALUES from your INSERT and make sure the SELECT selected fields match the … Read more
Does the trick of wrapping it into a derived table work for this case? (Based on http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/) DELETE FROM employee WHERE (empid, empssn) NOT IN ( SELECT empid, empssn FROM ( SELECT MIN(empid) AS empid, empssn FROM employee GROUP BY empssn ) X ); Edit Yep it seems to work this end.
Due to the locking implementation issues, MySQL does not allow referencing the affected table with DELETE or UPDATE. You need to make a JOIN here instead: DELETE gc.* FROM guide_category AS gc LEFT JOIN guide AS g ON g.id_guide = gc.id_guide WHERE g.title IS NULL or just use a NOT IN: DELETE FROM guide_category AS … Read more
Based on the information in the article you linked to this should work: update giveaways set winner=”1″ where Id = (select Id from (select max(Id) as id from giveaways) as t)
The problem is that MySQL, for whatever inane reason, doesn’t allow you to write queries like this: UPDATE myTable SET myTable.A = ( SELECT B FROM myTable INNER JOIN … ) That is, if you’re doing an UPDATE/INSERT/DELETE on a table, you can’t reference that table in an inner query (you can however reference a … Read more
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: http://dev.mysql.com/doc/refman/5.6/en/update.html Maybe you can just join … Read more