id_specific_price id_product
-------------------------------
1 2
2 2
3 2
4 3
5 3
6 3
7 3
Need to delete the duplicates, expected outcome:
id_specific_price id_product
-------------------------------
3 2
7 3
SELECT *
FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(id_specific_price)
FROM ps_specific_price
GROUP BY id_product)
works but
DELETE FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(id_specific_price)
FROM ps_specific_price
GROUP BY id_product)
does not. There are plenty of examples to get around this but for some reason I am not able to adapt it. I believe it is GROUP BY. For example:
DELETE FROM ps_specific_price
WHERE id_specific_price NOT IN
(SELECT MAX(p.id_specific_price)
FROM (SELECT * FROM ps_specific_price ) as p)
GROUP BY id_product
Where did I go wrong here?
SQL Delete Duplicate Rows using Group By and Having Clause According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.
To delete the duplicate rows from the table in SQL Server, you follow these steps: Find duplicate rows using GROUP BY clause or ROW_NUMBER() function. Use DELETE statement to remove the duplicate rows.
We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row. In the following query, we use a RANK function with the PARTITION BY clause.
If you're looking for a solution for MySQL then you can use a proper multi table DELETE
syntax along with a JOIN
like this
DELETE p
FROM ps_specific_price p JOIN
(
SELECT id_product, MAX(id_specific_price) id_specific_price
FROM ps_specific_price
GROUP BY id_product
) d
ON p.id_product = d.id_product
AND p.id_specific_price <> d.id_specific_price;
Outcome:
| ID_SPECIFIC_PRICE | ID_PRODUCT | |-------------------|------------| | 3 | 2 | | 7 | 3 |
Here is SQLFiddle demo
Try this:
CREATE TABLE ps_specific_price (
id_specific_price NUMBER,
id_product NUMBER
);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (1, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (2, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (3, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (4, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (5, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (6, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (7, 3);
COMMIT;
DELETE FROM ps_specific_price ps
WHERE ps.id_specific_price NOT IN (
SELECT MAX(id_specific_price)
FROM ps_specific_price ps_in
WHERE ps_in.id_product = ps.id_product
);
SELECT * FROM ps_specific_price;
ID_SPECIFIC_PRICE ID_PRODUCT
---------------------- ----------------------
3 2
7 3
You must connect the table from the inner query with the table from the outer one.
I'm using Oracle 11g R2. I checked this on SQLFiddle and my DELETE statement is invalid for MySQL - don't have that one installed and not much experience there, but you didn't say what database you are using.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With