Solved

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

Posted on 2014-03-09
9
1,060 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

770 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