Question 2426 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Delete duplicated items across different tables in MariaDB / SQL – WordPress

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search