Solved

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

Posted on 2014-03-09
9
1,068 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Init Waits 25 122
MSSQL 2008 with mySQL webservers 7 60
simple mysql statement 3 44
MySQL: Updating SubQuery Match Faster 9 53
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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