Reputation: 423
DELETE rows from two tables which do not have an id match to another query of the same two tables
The following does not necessarily use correct syntax, but is intended to convey my intention:
DELETE FROM wp_posts, wp_postmeta
WHERE wp_posts.ID <> min(f.post_ID), wp_postmeta.post_id <> min(f.post_ID)
(It's really two deletes combined in one, with the WHERE condition for the first and second table respectively.)
where min(f.post_ID) comes from the virtual table below:
SELECT Min(f.post_id),
f.post_title,
f.meta_value
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
How would the query look?
Edit: It's worth considering that any implementation that deletes from one table before the other will change the outcome of the second delete. (The second DELETE will be affected by the first delete because items from the table in the sub query were deleted in the first DELETE.)
Upvotes: 1
Views: 338
Answers (2)
Reputation: 23
The idea is to select IDs to be deleted from tables wp_posts and wp_postmeta into temporary tables posts and postmeta. This will preserve a list of IDs even if you delete data from any table. Then delete data from tables wp_posts and wp_postmeta one after another based on the list of IDs in the temp tables. The last step is to clear temporary tables (delete data from them).
The transaction is used in that way to allow run this code in PHPMyAdmin.
The optimal version of the code:
BEGIN;
CREATE TEMPORARY TABLE IF NOT EXISTS minPostIds AS
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price') AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title;
DELETE FROM wp_posts WHERE wp_posts.ID IN (SELECT ID FROM minPostIds);
DELETE FROM wp_postmeta WHERE wp_postmeta.post_id IN (SELECT ID FROM minPostIds);
DELETE FROM minPostIds;
COMMIT;
This version works:
BEGIN;
CREATE TEMPORARY TABLE IF NOT EXISTS posts AS
SELECT ID
FROM wp_posts
WHERE wp_posts.ID NOT IN
(
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
);
CREATE TEMPORARY TABLE IF NOT EXISTS postmeta AS
SELECT post_id
FROM wp_postmeta
WHERE wp_postmeta.post_id NOT IN
(
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
);
DELETE FROM wp_posts WHERE wp_posts.ID IN (SELECT ID FROM posts);
DELETE FROM wp_postmeta WHERE wp_postmeta.post_id IN (SELECT post_id FROM postmeta);
DELETE FROM posts;
DELETE FROM postmeta;
COMMIT;
You can also create a stored procedure:
CREATE PROCEDURE DeleteFromTables()
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS posts
ENGINE=MyISAM
AS (
SELECT ID
FROM wp_posts
WHERE wp_posts.ID NOT IN
(
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
)
);
CREATE TEMPORARY TABLE IF NOT EXISTS postmeta
ENGINE=MyISAM
AS (
SELECT post_id
FROM wp_postmeta
WHERE wp_postmeta.post_id NOT IN
(
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
)
);
START TRANSACTION;
DELETE FROM wp_posts
WHERE wp_posts.ID IN (SELECT ID FROM posts);
DELETE FROM wp_postmeta
WHERE wp_postmeta.post_id IN (SELECT post_id FROM postmeta);
COMMIT;
DELETE FROM posts;
DELETE FROM postmeta;
END;
Upvotes: 1
Reputation: 74605
You don't delete from two tables at the same time. If the tables are related, you delete from the child, then the parent. If they're unrelated the delete may happen in any order. If they're unrelated but have other tables depending on them (i.e. they are parents themselves and have children) then data must be cleared out of those other tables first. If the relational constraints are set to CASCADE DELETE mode, then child table data will be deleted automatically when parent table data is deleted. If the delete must happen as an all or nothing affair (i.e. if the second delete fails after the first delete succeeds you don't want the first delete to succeed), it should be done in a transaction.
Thus:
DELETE FROM wp_postmeta WHERE post_id NOT IN (
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
)
DELETE FROM wp_posts WHERE ID NOT IN (
SELECT Min(f.post_id)
FROM (SELECT wp_posts.post_title,
Min(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
GROUP BY wp_posts.post_title) AS x
INNER JOIN (SELECT wp_postmeta.post_id,
wp_posts.post_title,
wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.id
WHERE wp_posts.post_type = 'Product'
AND wp_postmeta.meta_key = '_regular_price'
ORDER BY wp_posts.post_title,
wp_postmeta.meta_value) AS f
ON x.post_title = f.post_title
AND f.meta_value = x.minprice
GROUP BY f.post_title
)
Warning
Don't run a delete query given to you by someone on the internet without backing your data up first. At the very least, start a transaction, run the delete, select the results and look at them to ensure they're correct, using the following code pattern:
START TRANSACTION;
DELETE FROM ...
DELETE FROM ...
SELECT * FROM ... -/*to check the deletes worked and didn't remove too much*/
ROLLBACK;
Change the ROLLBACK to COMMIT when you're happy
Edit:
Option 1
Make the posts meta depend on posts (it may do already, ensure the deletes are cascaded):
ALTER TABLE posts_meta
ADD CONSTRAINT fk_pm FOREIGN KEY (posts_id) REFERENCES posts(id) ON DELETE CASCADE
Now run your delete on the posts table, posts_meta entries will also disappear
Option2
Run your delete on posts table as recommended above
Use the following query to delete any record from posts_meta that doesnt have a matching record in posts:
DELETE FROM posts_meta WHERE post_id IN (select post_id from(
SELECT pm.post_id
FROM
posts_meta pm
LEFT JOIN
posts p
ON p.id = pm.post_id
WHERE
p.id IS NULL
) i )
The innermost subquery that finds the id list is wrapped inside another subquery for a reason; there are situations where MySQL will refuse a delete if the pattern is DELETE FROM x WHERE y IN (SELECT x FROM y) because you can't modify a table you're selecting from. Wrapping it up in another select is a hack that causes MySQL to not treat it as deleting from the same table you're selecting from
Upvotes: 1
Related Questions
- Delete sql rows where IDs do not have a match from another table
- Delete rows from both tables where id equals from both tables
- Two tables - how to delete rows if ID not referenced in both tables
- Deleting rows from one table if row exists in another table in MySQL
- Deleting records in MySQL where IDs from two tables match
- Delete sql rows with multiple values within where IDs do not have a match in another table
- DELETE values from one table with id that doesn't exist in another in mysql
- Mysql delete in one table by id's from another table
- MySQL: Delete row if id doesn't exist in other table
- DELETE data from two tables, based on a third's ID