Editing MySQL

Jump to navigation Jump to search

Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.

The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.

Latest revision Your text
Line 265: Line 265:
#Restart mysqld normally
#Restart mysqld normally
You are also able to reset the mysql root password by the help of the following line, while server is up:
[http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Resetting_permissions.html How to Reset a Forgotten Root Password]
# /etc/init.d/mysql reset-password
New MySQL root password:
Setting new MySQL root password
[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html How to Reset a Forgotten Root Password]
=== Making backups / mysqldump ===
=== Making backups / mysqldump ===
Line 367: Line 359:
=== IRC ===
=== IRC ===
Line 375: Line 367:
#[irc://efnet/mysql #mysql] on [[EfNet]]
#[irc://efnet/mysql #mysql] on [[EfNet]]
#[irc://freenode/mysql #mysql] on [[Freenode]]
#[irc://freenode/mysql #mysql] on [[Freenode]]
==== Group by Delete via temp table ====
<+mutante> hi, i do something like "select foo,bar,count(id) from table group by foo order by count(id);"
<+mutante> now i would like to "delete ... where count(id) (from above) = 1"
<+mutante> can i combine that?
<+mutante> i want to delete all rows where there is only one foo per bar
<+salle> mutante: Create temporary table out of the query above
<+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo, COUNT(id) AS c FROM tbl GROUP BY foo HAVING c = 1;
<+salle> mutante: CREATE TEMPORARY TABLE temp SELECT foo AS c FROM tbl GROUP BY foo HAVING c = 1;
<+salle> mutante: Now use that table in DELETE
<+salle> mutante: If it's so small MEMORY engine works perfect. If big it's wise to add some index on the fly
<+salle> mutante: DELETE FROM your_table WHERE foo IN (SELECT foo FROM temp); should work
<+salle> mutante: MySQL doesn't allow DELETE FROM x WHERE .. (SELECT ... FROM x)
<+salle> mutante: same for UPDATE. In many cases it is doable with multi-table DELETE and UPDATE syntax, but GROUP BY makes it more difficult if possible at all

Please note that all contributions to s23 are considered to be released under the Creative Commons Attribution Share Alike (see s23:Copyrights for details) unless another license is specified on a specific page. If you don't want your writing to be edited mercilessly and redistributed at will, then don't submit it here.
When uploading your own work you can choose from different cc license types, a GNU license like GFDL or you can declare it entirely Public domain. Use the according license template on a page. If no specific license is specified, Creative Commons Attribution Share Alike will apply. You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource. DO NOT SUBMIT COPYRIGHTED WORK WITHOUT PERMISSION!

Cancel Editing help (opens in new window)