Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

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

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
Nicolas Lagios
Asked:
Nicolas Lagios
  • 4
  • 3
  • 2
1 Solution
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
InsoftserviceCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Nicolas LagiosAuthor Commented:
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
 
InsoftserviceCommented:
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
 
Nicolas LagiosAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Nicolas LagiosAuthor Commented:
Its working, thank you very mutch, thank you....
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now