Solved

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

Posted on 2014-03-09
9
1,045 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

11 Experts available now in Live!

Get 1:1 Help Now