Delete duplicated items across different tables in MariaDB / SQL-CodePudding

I have a Wordpress Database with two tables with this structure

Table 1 "location"

id, postID, address

1, 123, park ave
2, 234, washington ave
3, 345, wall st
4, 456, park ave
5, 567, wall st

Table 2 "posts"

postID, title
123, "Foxtown restaurant"
234, "Tony's pizza"
345, "Roxy"
456, "Foxtown restaurant"
567, "Roxy"

(edited to make clear what "titles" are)

I need to DELETE the duplicated rows/items based on the "address" in table 1.

I do not care about whether to keep the newest or oldest data, that's irrelevant as they are records created twice by accident.

So, I tried this:

DELETE S1 FROM location AS S1  
INNER JOIN location AS S2   
WHERE S1.id > S2.id AND S1.address = S2.address;

But this removes the duplicated records in "location" (perfect) but keeps the records in "posts", simply without any associated address.

I tried doing something similar with "posts"

DELETE S1 FROM posts AS S1  
INNER JOIN posts AS S2   
WHERE S1.id > S2.id AND S1.title = S2.title;

But I realized that I cannot use the "title" feature in "posts" since there are records with the same title but a different address in table 1 (so, actually non-duplicated records).

For example I can have in location

6, 999, Brickell ave

And in posts

999, "Roxy"

Which IS NOT a duplicate, since it's another Roxy in another location.

Ideas?

CodePudding user response：

I believe you can set up your foreign key with ON DELETE CASCADE. That way, whenever a row in the parent table is deleted, any related rows in the child table will be deleted automatically.

Here is an example of a way to alter a table in order to create a foreign key with ON DELETE CASCADE

ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE CASCADE;

In your casen it would be something like this

ALTER TABLE location 
  ADD CONSTRAINT postID 
  FOREIGN KEY (postID) 
  REFERENCES posts(postID) 
  ON DELETE CASCADE;

CodePudding user response：

Besides an ON DELETE CASCADE you can do a classical DELETE FROM multiple Tables

CREATE TABLE location
    (`id` int, `postID` int, `address` varchar(14))
;
    
INSERT INTO location
    (`id`, `postID`, `address`)
VALUES
    (1, 123, 'park ave'),
    (2, 234, 'washington ave'),
    (3, 345, 'wall st'),
    (4, 456, 'park ave'),
    (5, 567, 'wall st')
;

Records: 5  Duplicates: 0  Warnings: 0

CREATE TABLE posts
    (`postID` int, `title` varchar(20))
;
    
INSERT INTO posts
    (`postID`, `title`)
VALUES
    (123, 'Foxtown restaurant'),
    (234, 'Tony''s pizza'),
    (345, 'Roxy'),
    (456, 'Foxtown restaurant'),
    (567, 'Roxy')
;

Records: 5  Duplicates: 0  Warnings: 0

DELETE L1,P1 FROM location AS L1  
INNER JOIN location AS L2   ON L1.id > L2.id AND L1.address = L2.address
INNER JOIN posts as P1 ON P1.postID = L1.postID

SELECT * FROM location

id	postID	address
1	123	park ave
2	234	washington ave
3	345	wall st

SELECT * FROM posts

postID	title
123	Foxtown restaurant
234	Tony's pizza
345	Roxy

fiddle