SQL UPDATE problem

[Rabatkoder]

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

[superprg]

The above query would not work since the column ps_product_lang.name does not belong to the table ps_category_product
Can you explain in brief what are you trying to do?

[Patric]

Topic moved. Please post in the good sections.

[Rabatkoder]

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

[superprg]

[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' )

[Rabatkoder]

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

[Rabatkoder]

Simple explained:

UPDATE `ps_category_product` SET `id_category` = 1292 WHERE `id_category` = 1250

But only if the product from category with the id 1250 includes the word "skjorte" in the product name ?

[siadmin]

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..