Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help, need sql query to delete column rows according to another column values

Posted on 2014-04-02
9
Medium Priority
?
593 Views
Last Modified: 2014-04-06
3. I need an sql query to search "wp_postmeta" table and delete all rows that has the same post_id that do not include or include partial of the following list of meta_key column:
extraship_Austria, extraship_Belarus, extraship_Belgium, extraship_Brazil, extraship_Canada
extraship_Cyprus, extraship_Czech Republic, extraship_Denmark, extraship_Estonia, extraship_Finland
extraship_France, extraship_Germany, extraship_Greece, extraship_Hungary, extraship_Iceland
extraship_India, extraship_Ireland, extraship_Israel, extraship_Italy, extraship_Latvia
extraship_Luxembourg, extraship_Malta, extraship_Monaco, extraship_Netherlands, extraship_New Zealand
extraship_Norway, extraship_Poland, extraship_Portugal, extraship_Qatar, extraship_Russia
extraship_San Marino, extraship_Spain, extraship_Sweden, extraship_Switzerland, extraship_Turkey
extraship_Ukraine, extraship_United Arab Emirates, extraship_United Kingdom, extraship_United States
extraship_Uruguay, extraship_Vatican City State (Holy See), extraship_Australia

Open in new window


I want to keep only post_id's that have the full list of meta_key column. It is 42 meta_key records.
Also the post_id record it is included also in wp_posts table in one row only.
I want that row also to be deleted from wp_posts table.
The column header of wp_postmeta table is "post_id" and in wp_posts table is "ID"
I don't know if this is possible but I hope it is.

Here is a big picture edited by me to make you understand the structure:
posts

I know I'm asking a lot and sorry about this. I will try to find what I'm looking for, even with php code but I hope to find a solution here.

Thanks in advance, Nicolas...
0
Comment
Question by:Nicolas Lagios
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39972970
In your screenshot, you show a post that has extraship_Australia and nothing else. Is that a record that would be deleted? Can you show us a similar screenshot of a record that should be kept?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39973910
Please provide sample data and expected results.

In the meantime does this help?
SELECT
    *
FROM `wp_posts`
WHERE `id` IN (
                SELECT
                    `post_id`
                FROM `wp_postmeta`
                WHERE meta_key IN (
                                      'extraship_Australia'
                                    , 'extraship_Austria'
                                    , 'extraship_Belarus'
                                    , 'extraship_Belgium'
                                    , 'extraship_Brazil'
                                    , 'extraship_Canada'
                                    , 'extraship_Cyprus'
                                    , 'extraship_Czech Republic'
                                    , 'extraship_Denmark'
                                    , 'extraship_Estonia'
                                    , 'extraship_Finland'
                                    , 'extraship_France'
                                    , 'extraship_Germany'
                                    , 'extraship_Greece'
                                    , 'extraship_Hungary'
                                    , 'extraship_Iceland'
                                    , 'extraship_India'
                                    , 'extraship_Ireland'
                                    , 'extraship_Israel'
                                    , 'extraship_Italy'
                                    , 'extraship_Latvia'
                                    , 'extraship_Luxembourg'
                                    , 'extraship_Malta'
                                    , 'extraship_Monaco'
                                    , 'extraship_Netherlands'
                                    , 'extraship_New Zealand'
                                    , 'extraship_Norway'
                                    , 'extraship_Poland'
                                    , 'extraship_Portugal'
                                    , 'extraship_Qatar'
                                    , 'extraship_Russia'
                                    , 'extraship_San Marino'
                                    , 'extraship_Spain'
                                    , 'extraship_Sweden'
                                    , 'extraship_Switzerland'
                                    , 'extraship_Turkey'
                                    , 'extraship_Ukraine'
                                    , 'extraship_United Arab Emirates'
                                    , 'extraship_United Kingdom'
                                    , 'extraship_United States'
                                    , 'extraship_Uruguay'
                                    , 'extraship_Vatican City State (Holy See)'
                                  )
                )

Open in new window

0
 

Author Comment

by:Nicolas Lagios
ID: 39975172
PortletPaul thank you for your time, I really appreciate.

Now according to this code I want to delete all the "post_id" (from wp_postmeta table) and "ID" (from wp_posts table) records that is not on the results of the code you gave me.

Is that possible?

Thank you, Nicolas...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39975441
Nicolas, the query that PortletPaul provided will show any posts that have ANY of those values, not ALL of those values, so you may want to verify that it returns the proper data you're expecting. According to your question, you were specific about partials:

delete all rows that has the same post_id that do not include or include partial of the following list of meta_key column

You can DELETE with a subquery:

DELETE FROM table WHERE id IN (SELECT id FROM other table WHERE x=y);

Basically you would change PortletPaul's query to use DELETE FROM instead of SELECT *.

MAKE SURE YOU PERFORM A FULL DATABASE BACKUP BEFORE YOU DO THIS!
0
 

Author Comment

by:Nicolas Lagios
ID: 39975680
gr8gonzo thank you very much for your time.

I didn't realise this, thank you.


So I am on a good way, for the time being the code removes all the records that don't have at least one of the meta_key records.

So now I need an edited code to remove the records that have a partial meta_key list, with the condition to keep the records that have the full list only.

I hope someone to help me with this, otherwise after 2 days I will mark this thread as Solved and I will accept Multiple Solutions from PortletPaul & gr8gonzo.

thank you very much for your time, anyway

Here is the code for the time being:
DELETE
    *
FROM `wp_posts`
WHERE `id` IN (
                SELECT
                    `post_id`
                FROM `wp_postmeta`
                WHERE meta_key IN (
                                      'extraship_Australia'
                                    , 'extraship_Austria'
                                    , 'extraship_Belarus'
                                    , 'extraship_Belgium'
                                    , 'extraship_Brazil'
                                    , 'extraship_Canada'
                                    , 'extraship_Cyprus'
                                    , 'extraship_Czech Republic'
                                    , 'extraship_Denmark'
                                    , 'extraship_Estonia'
                                    , 'extraship_Finland'
                                    , 'extraship_France'
                                    , 'extraship_Germany'
                                    , 'extraship_Greece'
                                    , 'extraship_Hungary'
                                    , 'extraship_Iceland'
                                    , 'extraship_India'
                                    , 'extraship_Ireland'
                                    , 'extraship_Israel'
                                    , 'extraship_Italy'
                                    , 'extraship_Latvia'
                                    , 'extraship_Luxembourg'
                                    , 'extraship_Malta'
                                    , 'extraship_Monaco'
                                    , 'extraship_Netherlands'
                                    , 'extraship_New Zealand'
                                    , 'extraship_Norway'
                                    , 'extraship_Poland'
                                    , 'extraship_Portugal'
                                    , 'extraship_Qatar'
                                    , 'extraship_Russia'
                                    , 'extraship_San Marino'
                                    , 'extraship_Spain'
                                    , 'extraship_Sweden'
                                    , 'extraship_Switzerland'
                                    , 'extraship_Turkey'
                                    , 'extraship_Ukraine'
                                    , 'extraship_United Arab Emirates'
                                    , 'extraship_United Kingdom'
                                    , 'extraship_United States'
                                    , 'extraship_Uruguay'
                                    , 'extraship_Vatican City State (Holy See)'
                                  )
                )

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 39976750
I think you do need to take particular care. The purpose of providing the select query was for you to check the results.

If you run that query, does it list the records you want to delete?

>> delete all rows that has the same post_id that do NOT include ... the following list of meta_key column
It is quite possible that the REVERSE is true, i.e. that the results of the select query will list those records you want to keep

Before proceeding;
Could you please tell us if the select query lists records you want to delete
, or does it list records you want to keep?

--------------
I wasn't sure what you meant by "partials"
Does it mean you have information such as this:

'extraship_Turke'
                                  and that would be a partial match for
'extraship_Turkey'

????



&& I second the excellent suggestion that you backup before deleting.
0
 

Author Comment

by:Nicolas Lagios
ID: 39976925
The results of the select query list is the records I want to keep.

Ok I have attach a pdf file that will help you understand, please check it.


mysql-sample.pdf


Here is the same sample in picture format:
mysql sample structure
0
 

Author Comment

by:Nicolas Lagios
ID: 39979740
anyone?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39980533
>>"The results of the select query list is the records I want to keep."

Then you need "NOT IN" (see line 4 below)
DELETE
    *
FROM `wp_posts`
WHERE `id` NOT IN (
                SELECT
                    `post_id`
                FROM `wp_postmeta`
                WHERE meta_key IN (
                                      'extraship_Australia'
                                    , 'extraship_Austria'
                                    , 'extraship_Belarus'
                                    , 'extraship_Belgium'
                                    , 'extraship_Brazil'
                                    , 'extraship_Canada'
                                    , 'extraship_Cyprus'
                                    , 'extraship_Czech Republic'
                                    , 'extraship_Denmark'
                                    , 'extraship_Estonia'
                                    , 'extraship_Finland'
                                    , 'extraship_France'
                                    , 'extraship_Germany'
                                    , 'extraship_Greece'
                                    , 'extraship_Hungary'
                                    , 'extraship_Iceland'
                                    , 'extraship_India'
                                    , 'extraship_Ireland'
                                    , 'extraship_Israel'
                                    , 'extraship_Italy'
                                    , 'extraship_Latvia'
                                    , 'extraship_Luxembourg'
                                    , 'extraship_Malta'
                                    , 'extraship_Monaco'
                                    , 'extraship_Netherlands'
                                    , 'extraship_New Zealand'
                                    , 'extraship_Norway'
                                    , 'extraship_Poland'
                                    , 'extraship_Portugal'
                                    , 'extraship_Qatar'
                                    , 'extraship_Russia'
                                    , 'extraship_San Marino'
                                    , 'extraship_Spain'
                                    , 'extraship_Sweden'
                                    , 'extraship_Switzerland'
                                    , 'extraship_Turkey'
                                    , 'extraship_Ukraine'
                                    , 'extraship_United Arab Emirates'
                                    , 'extraship_United Kingdom'
                                    , 'extraship_United States'
                                    , 'extraship_Uruguay'
                                    , 'extraship_Vatican City State (Holy See)'
                                  )
                )

Open in new window

BUT:


"As you can see, I want to
 delete all the yellow Rows
 that do not have all the
 Green Rows
"

that do not have ALL the green rows

This statement makes it harder to achieve, are you certain this is an accurate description?

For example if the last row  (Vatican City) of the righthand side table was not there then you would KEEP post 23137
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

972 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