Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-02
9
Medium Priority
?
591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The viewer will learn how to dynamically set the form action using jQuery.

718 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