asked on

MYSQL: EXISTS Syntax help

Hi

Given the example bellow each post_id can have multiple meta_key & meta_values

For each post_id where both meta_key = 'keywords' AND meta_key = 'keyword' I need to delete the row with meta_key = 'keywords'
so In the example rows 4 & 5 would go

Once I know either meta_key = 'keywords' or meta_key = 'keyword' exists I can run an update to change meta_key = 'keywords' to 'keyword'

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
);

INSERT INTO wp_postmeta VALUES
(1	,123	,'keyword'	,'xyz,abc,Something Else,Unique'),
(2	,124    ,'keyword'	,'xyz,abc,Something Else,Unique'),
(3	,125	,'keyword'	,'Experts, Exchange,Is Great,and Unique '),
(4	,123	,'keywords'	,'Hello World'),
(5	,124	,'keywords'	,'Hello World'),
(6	,126	,'keywords'	,'Experts, Exchange,Is Great,and Unique '),

Open in new window

Here is a select query for testing but isn't returning anything I'm expecting row 4 & 5

Select * FROM wp_postmeta WHERE meta_key = 'keywords' AND  meta_id in(
EXISTS (SELECT meta_id FROM wp_postmeta WHERE meta_key = 'keyword'and  meta_key = 'keywords' GROUP by post_id
        )
  )

Open in new window

ste5an

hmm, meta_key = 'keywords' AND meta_key = 'keyword' is a contradiction..

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Please use this solution.

Note you have to delete first then update the records.
Post_id -> 125 AND 126 will be deleted.

CREATE TABLE wp_postmeta1	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
);

INSERT INTO wp_postmeta1 VALUES
(1	,123	,'keyword'	,'xyz,abc,Something Else,Unique'),
(2	,124    ,'keyword'	,'xyz,abc,Something Else,Unique'),
(3	,125	,'keyword'	,'Experts, Exchange,Is Great,and Unique '),
(4	,123	,'keywords'	,'Hello World'),
(5	,124	,'keywords'	,'Hello World'),
(6	,126	,'keywords'	,'Experts, Exchange,Is Great,and Unique ')

DELETE T 
FROM wp_postmeta1 T
INNER JOIN 
(
	SELECT post_id
	FROM wp_postmeta1
	WHERE meta_key IN ('keyword','keywords')
	GROUP BY post_id
	HAVING COUNT(DISTINCT meta_key) <> 2
)k ON k.post_id = T.post_id 

UPDATE T 
SET T.meta_key = CASE WHEN T.meta_key = 'keywords' THEN 'keyword' ELSE T.meta_key END
FROM wp_postmeta1 T
INNER JOIN 
(
	SELECT post_id
	FROM wp_postmeta1
	WHERE meta_key IN ('keyword','keywords')
	GROUP BY post_id
	HAVING COUNT(DISTINCT meta_key) = 2
)k ON k.post_id = T.post_id

SELECT * FROM wp_postmeta1

Open in new window

OUTPUT AFTER DELETING AND UPDATING RECORDS

/*------------------------
SELECT * FROM wp_postmeta1
------------------------*/
meta_id     post_id     meta_key         meta_value
----------- ----------- ---------------- -------------------------------
1           123         keyword          xyz,abc,Something Else,Unique
2           124         keyword          xyz,abc,Something Else,Unique
4           123         keyword          Hello World
5           124         keyword          Hello World

(4 row(s) affected)

Open in new window

trevor1940

ASKER

Thanx that worked

@Pawan
Post_id -> 125 AND 126 will be deleted.
No row bellow needed deleting

(4 ,123 ,'keywords' ,'Hello World'),
(5 ,124 ,'keywords' ,'Hello World'),

Pawan Kumar

So you just needed was a select statement correct ?

So what is this
>>For each post_id where both meta_key = 'keywords' AND meta_key = 'keyword' I need to delete the row with meta_key = 'keywords'
so In the example rows 4 & 5 would go

I also gave you the select ....You need to change it to select...:)

SELECT *
FROM wp_postmeta1 T
INNER JOIN
(
      SELECT post_id
      FROM wp_postmeta1
      WHERE meta_key IN ('keyword','keywords')
      GROUP BY post_id
      HAVING COUNT(DISTINCT meta_key) = 2
)k ON k.post_id = T.post_id