MariaDB 10.1의 DELETE 구문 오류 원인을 알 수 없습니다.

MariaDB 10.1의 DELETE 구문 오류 원인을 알 수 없습니다.

아래 db Fielen의 코드는 MariaDB 10.1에서 실행될 때 다음과 같이 나타납니다.

오류:

SQL 구문에 오류가 있습니다.MariaDB 서버 버전에 대응하는 매뉴얼에서 'DELETE pm FROM WP_postmeta pm WHERE post_id IN(줄 26의 SELECT post_id ')' 근처에서 사용할 올바른 구문을 확인하십시오.

쿼리:

-- Block 1: Deleting all duplicate products in wp_posts table 
DELETE FROM wp_posts 
WHERE  id IN (SELECT id 
              FROM   (SELECT id, 
                             post_title, 
                             post_type, 
                             meta_value 
                      FROM   (SELECT wp_posts.id, 
                                     wp_postmeta.post_id, 
                                     post_title, 
                                     post_type, 
                                     meta_value, 
                                     Row_number() 
                                       OVER( 
                                         partition BY post_title 
                                         ORDER BY wp_postmeta.meta_value) rn 
                              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' 
                             ) t 
                      WHERE  t.rn <> 1) AS aliasx); 

-- Block 2: Deleting all corresponding wp_postmeta.post_ids that don't have a match in wp_posts.id after the duplicate deletion above
DELETE pm 
FROM   wp_postmeta pm 
WHERE  post_id IN (SELECT post_id 
                   FROM  (SELECT post_id 
                          FROM   wp_postmeta pm 
                                 LEFT JOIN wp_posts p 
                                        ON p.id = pm.post_id 
                          WHERE  p.id IS NULL 
                                 AND p.post_type = "product") i) 

이 오류를 수정하려면 어떻게 해야 하나요?첫 번째와 두 번째 코드 블록은 단독으로 입력했을 때 오류가 발생하지 않지만 함께 입력하면 오류가 발생합니다.

WordPress에서 사용하는 MySQL API에서는 두 개의 연결된 쿼리(이 경우 두 개의 DELETE)를 하나로 제공할 수 없습니다.dbfiddle도 마찬가지입니다.쿼리를 개별적으로 실행합니다.

다른 테이블 서버에 익숙한 경우 혼란스러울 수 있습니다.

실제로는 분할하여 동작합니다.Delete진술들

create table wp_posts (
  ID integer primary key auto_increment,
  post_title varchar(30),
  post_type varchar(30)
);

✓

create table wp_postmeta (
  ID integer primary key auto_increment,
  post_id integer,
  meta_key varchar(30) not null default '_regular_price',
  meta_value integer not null
);

✓

insert into wp_posts (post_title, post_type) values
('Apple Pie','Product'),
('French Toast','Product'),
('Shepards Pie','Product'),
('Jam Pie','Product'),
('Jam Pie','Product'),
('Plate','Not a Product'),
('Bucket','Not a Product'),
('Chequebook','Not a Product'),
('French Toast','Product'),
('French Toast','Product'),
('Banana','Product'),
('Banana','Product'),
('Banana','Product');

✓

insert into wp_postmeta (post_id, meta_value) values
(1,10),
(2,5),
(3,9),
(4,8),
(5,11),
(6,12),
(7,10),
(8,6),
(9,1),
(10,1),
(11,7),
(12,2),
(13,2);

✓

-- Deleting all duplicate products in wp_posts table 
DELETE FROM wp_posts 
WHERE  id IN (SELECT id 
              FROM   (SELECT id, 
                             post_title, 
                             post_type, 
                             meta_value 
                      FROM   (SELECT wp_posts.id, 
                                     wp_postmeta.post_id, 
                                     post_title, 
                                     post_type, 
                                     meta_value, 
                                     Row_number() 
                                       OVER( 
                                         partition BY post_title 
                                         ORDER BY wp_postmeta.meta_value) rn 
                              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' 
                             ) t 
                      WHERE  t.rn <> 1) AS aliasx); 

✓

-- Deleting all corresponding wp_postmeta.post_ids that don't have a match in wp_posts.id after the duplicate deletion above
DELETE pm 
FROM   wp_postmeta pm 
WHERE  post_id IN (SELECT post_id 
                   FROM  (SELECT post_id 
                          FROM   wp_postmeta pm 
                                 LEFT JOIN wp_posts p 
                                        ON p.id = pm.post_id 
                          WHERE  p.id IS NULL 
                                 AND p.post_type = "product") i) 

✓

db <>여기에 추가

언급URL : https://stackoverflow.com/questions/54560118/unknown-cause-of-syntax-error-for-delete-in-mariadb-10-1