MySQL ‘UPDATE ON DUPLICATE KEY’ without a unique column?

You can create a PRIMARY or UNIQUE key out of multiple columns (called a composite key) in MySQL, which’ll allow ON DUPLICATE KEY to work just fine. // create a composite index CREATE INDEX my_composite_index ON my_table (column1, column2); // insert or update INSERT INTO my_table (column1, column2) VALUES (‘value1’, ‘value2’) ON DUPLICATE KEY UPDATE … Read more

How does MySQL’s ORDER BY RAND() work?

While there’s no such thing as a “fast order by rand()”, there is a workaround for your specific task. For getting any single random row, you can do like this german blogger does: http://web.archive.org/web/20200211210404/http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/ (I couldn’t see a hotlink url. If anyone sees one, feel free to edit the link.) The text is in german, … Read more

MySQL Insert Into from one Database in another

Your query should go like this: INSERT INTO newDatabase.table1 (Column1, Column2) SELECT column1, column2 FROM oldDatabase.table1; UPDATE Since this answer is getting more attention than I even anticipated, I should expand on this answer. First of all, it might not be obvious from the answer itself, but the columns do not need to have the … Read more

Mysql select distinct

DISTINCT is not a function that applies only to some columns. It’s a query modifier that applies to all columns in the select-list. That is, DISTINCT reduces rows only if all columns are identical to the columns of another row. DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following … Read more

Is merge statement available in MySQL

MERGE is not supported by MySQL, However, there is other possible way of doing the same: INSERT…ON DUPLICATE KEY UPDATE If you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old … Read more