?
Solved

MYSQL: EXISTS Syntax help

Posted on 2017-12-07
5
Medium Priority
?
40 Views
Last Modified: 2017-12-07
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

0
Comment
Question by:trevor1940
5 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 42395288
hmm, meta_key = 'keywords' AND meta_key = 'keyword'  is a contradiction..
0
 
LVL 14

Accepted Solution

by:
Máté Farkas earned 2000 total points
ID: 42395300
SELECT * FROM wp_postmeta 
WHERE meta_key = 'keywords' 
AND  meta_id IN (
        SELECT meta_id FROM wp_postmeta 
        WHERE meta_key = 'keywords'
)

Open in new window

0
 
LVL 37

Expert Comment

by:Pawan Kumar
ID: 42395303
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

0
 
LVL 1

Author Closing Comment

by:trevor1940
ID: 42395339
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'),
0
 
LVL 37

Expert Comment

by:Pawan Kumar
ID: 42395344
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
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Join & Write a Comment

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question