Solved

PhPmyadmin SQL Query to insert meta_key & value IF another value exist

Posted on 2014-03-09
9
1,086 Views
Last Modified: 2014-03-10
Ok I am looking for an sql query to insert meta_key & value in a specific table IF another value exist.

I am new with phpmyadmin and I really need help.

This is what I have so far:

SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE 'qty' AND `meta_value` LIKE '0'

Open in new window

the previous query is only to filter the meta_key values.
I have also try to create something with my little knowledge but also is not working:

INSERT INTO `wp_postmeta` WHERE `meta_key` LIKE 'qty' AND `meta_value` LIKE '0'(`meta_key`, `meta_value`) VALUES ([_wplp_post_front],[1])

Open in new window


I need an sql query to do this:
1. Search the wp_postmeta table for posts with meta_key "qty" and "0" meta_value
2. after that I need to insert to those posts a new meta_key "_wplp_post_front" with value "1"

I'm sure this is a simple query but I can't work it out!

Many thanks,

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:Terry Woods
ID: 39916638
You probably want something like this:
INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`
 WHERE `meta_key` LIKE 'qty'
 AND `meta_value` LIKE '0'

Open in new window

You'll need to list the columns that you want to copy data for instead of using "another_column, yet_another_column".

Note that you should have a backup of your data before you run any suggested queries!

Also, this will only be able to be run once otherwise you'll get duplicate data in your table.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39916647
To avoid duplicate data, if you want to be able to safely re-run the query, you can do something like this:
INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`outside_table
 WHERE `meta_key` LIKE 'qty'
 AND `meta_value` LIKE '0'
 AND NOT EXISTS 
(SELECT * FROM `wp_postmeta` 
WHERE some_column = outside_table.some_column --change this column name to something sensible
AND `meta_key` = "_wplp_post_front"
AND `meta_value` = 1
)

Open in new window

0
 
LVL 15

Expert Comment

by:Insoftservice
ID: 39916906
Why it can't be

 SELECT * FROM `wp_postmeta` WHERE `meta_key` ='qty' AND `meta_value` =0

Slight changes in insert query of above EE comment

INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`outside_table
 WHERE `meta_key` ='qty'
 AND `meta_value` =0
 AND NOT EXISTS
(SELECT * FROM `wp_postmeta`
WHERE some_column = outside_table.some_column --change this column name to something sensible
AND `meta_key` = "_wplp_post_front"
AND `meta_value` = 1
)
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:Nicolas Lagios
ID: 39917797
TerryAtOpus
INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`
 WHERE `meta_key` LIKE 'qty'
 AND `meta_value` LIKE '0'
Its not working for me, i am getting error: #1054 - Unknown column 'another_column' in 'field list'

TerryAtOpus
To avoid duplicate data, if you want to be able to safely re-run the query, you can do something like this:

INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`outside_table
 WHERE `meta_key` LIKE 'qty'
 AND `meta_value` LIKE '0'
 AND NOT EXISTS
(SELECT * FROM `wp_postmeta`
WHERE some_column = outside_table.some_column --change this column name to something sensible
AND `meta_key` = "_wplp_post_front"
AND `meta_value` = 1
)

It's not working either, i am getting the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change this column name to something sensible
AND `meta_key` = "_wplp_post_fron' at line 8


insoftservice
Why it can't be

 SELECT * FROM `wp_postmeta` WHERE `meta_key` ='qty' AND `meta_value` =0

Slight changes in insert query of above EE comment

INSERT INTO `wp_postmeta` (`meta_key`, `meta_value`, `another_column`, `yet_another_column`)
SELECT "_wplp_post_front", 1, another_column, yet_another_column
 FROM `wp_postmeta`outside_table
 WHERE `meta_key` ='qty'
 AND `meta_value` =0
 AND NOT EXISTS
(SELECT * FROM `wp_postmeta`
WHERE some_column = outside_table.some_column --change this column name to something sensible
AND `meta_key` = "_wplp_post_front"
AND `meta_value` = 1
)

Also its not working I am getting the same errors.


I really need help on this.

My database is a Wordpress database and this is what i want:

wp_postmeta Table have 4 columns as i can see (meta_id , post_id , meta_key , meta_value)

Im using an ecommerce plugin and every week Im importing a csv file with 1500+ product posts, the most of the products exist and the post_id remains the same, the only thing that changes in those posts is the meta_value of qty meta_key from "100" to "0".

4 days ago I found a wordpress plugin called "WP Hide Posts" and this plugin have the ability to manual hide the posts that are out of stock (qty 0) and I like it very mutch because I only hide the out of stock posts instead of deleting them.

If I manual hide a post, the WP Hide Plugin creates a meta_key called "_wplp_post_front" and a meta_value "1" in wp_postmeta table.


So the problem is that I want an SQL query or a php code that can create the "_wplp_post_front" meta_key with value "1" in wp_postmeta table for all the posts that have the meta_key "qty" with meta_value "0"

Is there any chance for a ready made sql query or php code to do this?


Many thanks,

Nicolas
0
 
LVL 15

Expert Comment

by:Insoftservice
ID: 39918123
what was o/p of  
SELECT * FROM `wp_postmeta` WHERE `meta_key` ='qty' AND `meta_value` =0

schema of your required table for eg wp_postmeta;
0
 

Author Comment

by:Nicolas Lagios
ID: 39918554
what do you mean? i can't understand.
here i have 3 captured screens from my phpmyadmin

the first screen is from wp_postmeta table
the first screen is from wp_postmeta table

the second screen is on the same table after i made search by post id
As you can see on this screen if the qty is 0 i need a new meta_key "_wplp_post_front" and a meta_value "1" to be created.
the second screen is on the same table after i made search by post id

and this is the third screen after i execute the query
SELECT * FROM `wp_postmeta` WHERE `meta_key` ='qty' AND `meta_value` =0

Open in new window

and this is the third screen after i execute the query
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39918888
You're getting the error
#1054 - Unknown column 'another_column' in 'field list'
because there exists no column called "another_column", which is to be expected!

As I stated: You'll need to list the columns that you want to copy data for instead of using "another_column, yet_another_column".

It's also clear you want to use = instead of LIKE.

The screenshots you've provided seem to show that the primary key for the table is "meta_id", which we probably won't want to specify when inserting data (it's likely to be automatically assigned), which would mean this is the query you want to run:
INSERT INTO wp_postmeta (meta_key, meta_value, post_id)
SELECT "_wplp_post_front", 1, post_id
 FROM wp_postmeta
 WHERE meta_key = 'qty'
 AND meta_value = '0'

Open in new window

0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39918910
To avoid duplicates when re-running the query, it would need to be changed like this:

INSERT INTO wp_postmeta (meta_key, meta_value, post_id)
SELECT "_wplp_post_front", 1, post_id
 FROM wp_postmeta outside_table
 WHERE meta_key = 'qty'
 AND meta_value = '0'
 AND NOT EXISTS 
(SELECT * FROM wp_postmeta
WHERE post_id = outside_table.post_id
AND meta_key = "_wplp_post_front"
AND meta_value = 1
)

Open in new window


Again, please ensure you have a backup of your data; don't just assume my queries are perfect (because they're not always!)
0
 

Author Comment

by:Nicolas Lagios
ID: 39919258
Its working, thank you very mutch, thank you....
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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