Solved

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

Posted on 2014-04-02
9
574 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 34

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 48

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
 
LVL 34

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 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 48

Accepted Solution

by:
PortletPaul earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now