You can lock tables using the MySQL LOCK TABLES command like this: LOCK TABLES tablename WRITE; # Do other queries here UNLOCK TABLES; See: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
Some reference for you http://dev.mysql.com/doc/refman/5.0/en/update.html UPDATE user_account student INNER JOIN user_account teacher ON teacher.user_account_id = student.teacher_id AND teacher.user_type=”ROLE_TEACHER” SET student.student_education_facility_id = teacher.education_facility_id
Try in this way DELETE FROM recent_edits WHERE trackid NOT IN (select * from (SELECT DISTINCT history.trackid FROM history JOIN recent_edits ON history.trackid=recent_edits.trackid GROUP BY recent_edits.trackid) as t);
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
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
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