delete from MYSQL

How i can delete something from MYSQL

i didn't put an unique ID on the table...

so i can't do

DELETE FROM contacts WHERE id=XX

I am just fetching the data into a PHP script
and i would like to remove them line, by line

but a search function like where will not be very good
because... i would like to remove some data from the databases just because it's duplicated data....

 

If it is just because it is duplicate data, you can simply limit to 1 less than the total rows:

$totalRows = mysql_num_rows(mysql_query('SELECT * FROM tableName WHERE someField="someValue" AND anotherField="anotherValue"'));

if ($totalRows >= 2){
   mysql_query('DELETE FROM tableName WHERE someField="someValue" AND anotherField="anotherValue" LIMIT '.($totalRows-1));
}

PHP:

(The code above is untested, if there are any typos my apologies)

 

Test table

CREATE TABLE dupetest (
  a varchar(1) NOT NULL,
  b varchar(1) NOT NULL,
  c varchar(1) NOT NULL
)

Code (sql):

Test data

INSERT INTO dupetest (a, b, c) VALUES 
('a', 'a', 'a'),
('b', 'b', 'b'),
('c', 'c', 'c'),
('d', 'd', 'd'),
('a', 'a', 'a'),
('c', 'c', 'c');

Code (sql):

Add a temp key

ALTER TABLE `dupetest`
ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

Code (sql):

Remember the value returned from this. In the case of the table above it is "6".

SELECT MAX( id )
FROM dupetest

Code (sql):

Append distinct/unique rows to the end of the table

INSERT INTO dupetest (a,b,c)
SELECT DISTINCT a,b,c FROM dupetest

Code (sql):

Delete old data

DELETE FROM dupetest
WHERE id <= 6

Code (sql):

Get rid of temp key

ALTER TABLE `dupetest` DROP `id`

Code (sql):

Have MySQL return an error when a duplicate row as about to be inserted.

ALTER TABLE `dupetest`
ADD PRIMARY KEY ( `a` , `b` , `c` )

Code (sql):