Suppose your table is called ingredients
. Try the following:
Step 01) Create an empty delete keys table called ingredients_delete_keys
CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;
Step 02) Create PRIMARY KEY on ingredients_delete_keys
ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);
Step 03) Index the ingredients
table with fk,recipe,pkey
ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);
Step 04) Populate the ingredients_delete_keys
table
INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;
Step 05) Perform a DELETE JOIN on ingredients table using keys that don't match
DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;
Step 06) Drop the delete keys
DROP TABLE ingredients_delete_keys;
Step 07) Get rid of the fk_recipe_pkey_ndx
index
ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;
OK Here are all the lines in one block...
CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;
ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);
ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);
INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;
DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;
DROP TABLE ingredients_delete_keys;
ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;
Give it a Try !!!
CAVEAT
Notice that using MIN function helps keep the first pkey entered for fk. If you switch it to MAX function instead, the last pkey entered for fk is kept.
I would use a temp table
#
# Collect All IDs Your Intend to Keep
#
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO KeepIDList (id) SELECT min_id FROM
(SELECT email,MIN(id) min_id FROM users GROUP BY email) A;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# Collect All IDs Your Intend to Delete
#
CREATE TABLE ZapIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO ZapIDList (id)
SELECT A.id FROM users A LEFT JOIN KeepIDList B WHERE B.id IS NULL;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# From Collected Keys to Delete, Remove Keys Whose chips > 0
#
DELETE B.* FROM users A INNER JOIN ZapIDList B WHERE A.chips > 0;
#
# From Collected Keys Left to Delete, Perform DELETE JOIN
#
DELETE A.* FROM users A INNER JOIN ZapIDList B;
#
# Drop Temp Tables
#
DROP TABLE KeepIDList;
DROP TABLE ZapIDList;
I have a very good reason for using temp tables as opposed to doing a Direct DELETE JOIN of a table against itself: Performing a DELETE using a subquery that references the same table experiencing the DELETE tends to make keys disappear intermittently as the query is being optimized. I wrote about this back on Feb 22, 2011 : Problem with MySQL subquery
CAVEAT
Please test this out with a smaller dataset to make sure my answer indeed performs what you asked. Make sure you make s backup of the users
table. To coin a phrase from 1960's Mission Impossible: Should any of your data be caught or killed, I will disavow any knowledge of your actions (Start From 1:50 of the YouTube Video).
Best Answer
INSERT
andINSERT IGNORE
add a new row unless the row specifies a duplicate for somePRIMARY
orUNIQUE
key on the table. The only difference is whether the "duplicate key" is an error or ignored.Do you want one row per user? Or can the user provide many passwords? In the former case,
PRIMARY KEY(username)
; in the latter,PRIMARY KEY(username, password)
.Do you want the user to be able to change his password? Then consider:
But... In any case escape both the username and password, else a username such as
O'Brien
will blow up your program. (And a hacker can quickly take over your machine by quickly discovering you have failed to escape stuff from$_POST
.)