Jump to content

SQL UPDATE problem


Rabatkoder
 Share

Recommended Posts

Hello everyone i'm going nuts.
I have imported a csv file that mixed my products so i want to split some off the products in to a seperate category. I'm trying to do that with this sql code:

UPDATE 'ps_category_product' SET 'id_category' = 1292 WHERE 'id_category' = 1250 AND 'ps_product_lang.name' = "skjorte"


i'm a jerk to coding (and english) sorry, and can't figure out how to do this right :(

Share this post


Link to post
Share on other sites

I'm trying to search for products including the word "skjorte" in product name.
If they include the word then i would like to transfer them to another category :)

But the problem is that the category id is in ps_category_product and the product name is in ps_product_lang

Share this post


Link to post
Share on other sites

[Please take backup before running any of these queries in the DB]

To find products which have name as skjorte

select * from ps_product_lang where name ='skjorte'



To find products which have any of the word 'skjorte' in the name

select * from ps_product_lang where name like 'skjorte'



Now to update, use joins or nested query

update ps_category_product set id_category='' where id_product in (select id_product from ps_product_lang where name like 'skjorte' )

Share this post


Link to post
Share on other sites

Thanks alot but the problem is that i have many products with the name skjorte, but some off them is in the wrong category.

So if i search for the word "skjorte" in every categories i will transfer all products including that word to the specified category?

that why i tried this code:
UPDATE ‘ps_category_product’ SET ‘id_category’ = 1292 WHERE ‘id_category’ = 1250 AND ‘ps_product_lang.name’ = “skjorte”

In my head i'm thinking that if i search for the word "skjorte" in the category that has the id 1250, then it will transfer all products from category_id 1250 including keyword "skjorte" to category id 1292?

i am sorry for my bad and confusing english :)

Share this post


Link to post
Share on other sites

you need something more than SQL i think, i would do it like this.. excuse the potentially dogdy syntax..

..PHP

$query = "SELECT * FROM ps_product_lang WHERE name='value'";
$row = mysql_fetch_array(mysql_query($query);

while($row = mysql_fetch_array(mysql_query($query)){

$update = "UPDATE ps_cat_prod SET cat_id='value' WHERE prod_id=" . $row['prod_id'];
mysql_query($update);

}



remember back up your database while testing this..

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More