Deleting rows with the same value in a column

[Jessica]

If I have a table that has columns

id (primary key), name (vchar) and then (other columns that don't matter)

 

And I can do this query to get all the names which exist more than once.

 

SELECT count(id), name
FROM tbl
GROUP BY name
HAVING count(name) >1

 

I get a number of rows that are 3, name

 

I want to delete 2 of the 3 rows, starting with the ones with the higher IDs.

 

Is there any way to do this in a query? I tried doing a subselect in the delete, but get an error and I'm stuck on the syntax. This would also delete all three if it worked I guess.

 

DELETE FROM tbl
WHERE name IN (
    SELECT name
    FROM tbl b
    GROUP BY name
    HAVING count(name) >1
)

 

Error

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ....

[jazzman1]

I'm thinking that it's good idea to use - Date and Time Functions in MySQL -> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Your question looks like very easy, but actually is not   

[xyph]

Well, you can't really delete a grouped result

 

Is this a query you're going to execute all the time, or just once to clean up your DB?

 

I'd create a new table, with vchar being unique.

 

INSERT IGNORE INTO temp_table (id, name, other_columns)

SELECT id, name, other_columns FROM old_table

 

Then you DROP the old table, and rename the new one.

[mikosiko]

for a one time shoot you can try this alternative.... tricky but seems to do the job

 

CREATE TEMPORARY TABLE xx
   (SELECT tbl.id FROM tbl
      JOIN (SELECT id,name
              FROM tbl
              GROUP BY name
              HAVING count(id) > 1) AS X
        ON tbl.id != X.id AND tbl.name = X.name);

DELETE FROM tbl
WHERE id IN (select id from xx);

[Jessica]

I fixed the table to make the name column unique, this was just a cleanup. I know I can't delete the grouped result, I just want to use the information FROM it to determine what to delete.

 

I eventually just got the list of IDs I wanted to delete and put them in the IN() on the delete.

[xyph]

Well, in the future, my method saves you from having to do that

[Jessica]

I thought about making  a new table, but I was hoping to avoid it if I could. It does seem to be the easiest way to do it though.

 

Hopefully I won't come across this situation again but if I do, I'll probably just do that

[jazzman1]

With mysql procedure would  be working too, but it's a little bit complicated:

Run the code through mysql console:

Example:

DELIMITER ;;
DROP PROCEDURE IF EXISTS `DeleteMaxID`;;
CREATE PROCEDURE `DeleteMaxID`()
BEGIN
  DECLARE MaxID smallint;
  SET MaxID = (SELECT max(`table_name`.`user_id`) from `table_name`);
  DELETE FROM `table_name` WHERE `table_name`.`id` = MaxID;
END
;;
DELIMITER ;

[mikosiko]

With mysql procedure would  be working too, but it's a little bit complicated:

Run the code through mysql console:

Example:

DELIMITER ;;
DROP PROCEDURE IF EXISTS `DeleteMaxID`;;
CREATE PROCEDURE `DeleteMaxID`()
BEGIN
  DECLARE MaxID smallint;
  SET MaxID = (SELECT max(`table_name`.`user_id`) from `table_name`);
  DELETE FROM `table_name` WHERE `table_name`.`id` = MaxID;
END
;;
DELIMITER ;

 

that is not what she wanted to do... read the OP again

[xyph]

I thought about making  a new table, but I was hoping to avoid it if I could. It does seem to be the easiest way to do it though.

 

Hopefully I won't come across this situation again but if I do, I'll probably just do that

 

It's a terrible solution for something that has to be performed regularly, but ideal for a one-time rebuild. Now that I think about it, it should never have to be performed regularly

[jazzman1]

that is not what she wanted to do... read the OP again

 

Why ? Once created a stored procedure in database, she could use and run a PHP Data Objects (PDO) to execute the logic of this procedure.

Something like this (of course I improvise, sorry about my EN, too ),

 

class PDOConnection 
{
public static $connection; 

public static function getConnection()
{
  if (!isset(PDOConnection::$connection))
  {
   PDOConnection::$connection = new PDO('mysql:host=' . HOST. ';dbname=' . DBNAME , USER, PASS);
  }
  
  return PDOConnection::$connection;
}

}

$pdoconn = PDOConnection::getConnection();

$stmt = $pdoconn->prepare("call DeleteMaxID()");

$stmt->execute();

unset($stmt);

// Or pass an ID to DeleteMax() method

$stmt = $pdoconn->prepare("call DeleteMaxID(:var)");

$stmt->bindParam(":car",$var_in_php, PDO::PARAM_STR);

$stmt->execute();

unset($stmt);