I think the fastest solution is select * from table where rand() <= .3 Here is why I think this should do the job. It will create a random number for each row. The number is between 0 and 1 It evaluates whether to display that row if the number generated is between 0 and … Read more
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
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)
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
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
You need to edit your my.cnf file and make sure you have the port set as in the following line: port = 3360 Then restart your MySQL service and you should be good to go. There is no query you can run to make this change because the port is not a dynamic variable (q.v. … Read more
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
I also had same problem in past. All had happend after moving database files to new location and after updating mysql server. All tables with InnoDB engine disappeared from my database. I was trying to recreate them, but mysql told me 1146: Table ‘xxx’ doesn’t exist all the time until I had recreated my database … Read more
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
In CRUD operations, the INSERT is the ‘C’ and the UPDATEis the ‘U’. They are two of the four basic functions of persistent storage. The other two are SELECT and DELETE. Without at least these four operations, a typical database system cannot be considered complete. Use INSERT to insert a new record. Use UPDATE to … Read more