I
I
invisii2020-05-20 10:49:57
MySQL
invisii, 2020-05-20 10:49:57

How to delete rows of another table with SQL query?

Good day. Actually the usual WP base.
wp_postmeta table:

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(3, 1, 'custom_permalink', '2018/03/TITLE1.html'),
(9, 2, 'custom_permalink', '2017/11/TITLE2-KEY.html'),
(15, 3, 'custom_permalink', '2017/09/TITLE3.html'),

wp_posts table:
INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(1, 1, '2018-03-25 16:44:42', '2018-03-25 16:44:42', 'TEXT1', 'TITLE1', '', 'publish', 'open', 'open', '', '1', '', '', '2020-01-01 12:42:15', '2020-01-01 12:42:15', '', 0, '/?p=1', 0, 'post', '', 0),
(2, 1, '2017-11-03 10:37:19', '2017-11-03 10:37:19', 'TEXT2', 'TITLE2', '', 'publish', 'open', 'open', '', '2', '', '', '2020-01-01 12:42:16', '2020-01-01 12:42:16', '', 0, '/?p=2', 0, 'post', '', 0),
(3, 1, '2017-09-29 11:17:03', '2017-09-29 11:17:03', 'TEXT3', 'TITLE3', '', 'publish', 'open', 'open', '', '3', '', '', '2020-01-01 12:42:16', '2020-01-01 12:42:16', '', 0, '/?p=3', 0, 'post', '', 0),


Tell me how to make a query to find in the wp_postmeta table the lines containing "-KEY.html" in the url and delete these posts from the wp_posts table?
My request fails:
DELETE FROM wp_posts 
WHERE ID = 
(SELECT post_id FROM wp_postmeta WHERE meta_value LIKE '%-KEY.html%');

Answer the question

In order to leave comments, you need to log in

1 answer(s)
T
ThunderCat, 2020-05-20
@invisii

you assign id a set of IDs instead of a single value, if you want your construction to work - use in()

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question