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
Try DISABLE KEYS or SET FOREIGN_KEY_CHECKS=0; Make sure to SET FOREIGN_KEY_CHECKS=1; after.
Yes you can: SET FOREIGN_KEY_CHECKS = 0; TRUNCATE table1; TRUNCATE table2; SET FOREIGN_KEY_CHECKS = 1; With these statements, you risk letting in rows into your tables that do not adhere to the FOREIGN KEY constraints.
To copy with indexes and triggers do these 2 queries: CREATE TABLE new_table LIKE old_table; INSERT INTO new_table SELECT * FROM old_table; To copy just structure and data use this one: CREATE TABLE new_table AS SELECT * FROM old_table; I’ve asked this before: Copy a MySQL table including indexes
Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”)); you can read more here Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin Open phpmyadmin & select localhost Click on menu Variables & scroll down for sql mode Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.
Possibly a security precaution. You could try adding a new administrator account: mysql> CREATE USER ‘monty’@’localhost’ IDENTIFIED BY ‘some_pass’; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’ -> WITH GRANT OPTION; mysql> CREATE USER ‘monty’@’%’ IDENTIFIED BY ‘some_pass’; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’ -> WITH GRANT OPTION; Although as Pascal and others … Read more
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields: SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM. Here is a small overview of which db storage engine you should use in which situation: MyISAM InnoDB —————————————————————- Required full-text search Yes 5.6.4 —————————————————————- Require transactions Yes —————————————————————- Frequent select queries Yes —————————————————————- Frequent … Read more
TEXT and BLOB may by stored off the table with the table just having a pointer to the location of the actual storage. Where it is stored depends on lots of things like data size, columns size, row_format, and MySQL version. VARCHAR is stored inline with the table. VARCHAR is faster when the size is … Read more
Do a SELECT with a GROUP BY clause. Let’s say name is the column you want to find duplicates in: SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1; This will return a result with the name value in the first column, and a count of how many times that value … Read more