mysql update based on multiple data in one column

[sasori]

I'm trying this update, but everything is getting updated. I'm trying to exclude 7 items from the update. What am I doing wrong?

update
products inner join products_categories on products_categories.pid = products.pid
SET products.price2 = products.price,
products.price = products.price*0.75
WHERE
products_categories.cid != "138"
AND
products_categories.cid != "128"
AND
products_categories.cid != "129"
AND
products_categories.cid != "130"
AND
products_categories.cid != "131"
AND
products_categories.cid != "133"
AND
products_categories.cid != "132"

[Demaestro]

It might be because you are updating products but your where statement only mentions products_categories.

I know you have joined them but it could be that the db wants the conditions to be set on the same table that you are performing the update on.

Try this.

update
products inner join products_categories on products_categories.pid = products.pid
SET products.price2 = products.price,
products.price = products.price*0.75
WHERE
products.cid != "138"
AND
products.cid != "128"
AND
products.cid != "129"
AND
products.cid != "130"
AND
products.cid != "131"
AND
products.cid != "133"
AND
products.cid != "132"

You can reduce the where clause to 1 statement like this:

update
products inner join products_categories on products_categories.pid = products.pid
SET products.price2 = products.price,
products.price = products.price*0.75
WHERE
products.cid not in (138, 128, 129, 130, 131, 133, 132)

[edited by: Demaestro at 7:36 pm (utc) on June 18, 2009]

[sasori]

unfortunately, the cids to exclude are in products_categories.
However, your code is much nicer.
I put products_categories.cid in your WERE, but the same thing happened.

Doing a SELECT generates the list based on the WHERE

Thanks

[LifeinAsia]

What about:
UPDATE products SET
price2 = price,
price = price*0.75
FROM products inner join products_categories on products_categories.pid = products.pid
WHERE
products_categories.cid not in (138, 128, 129, 130, 131, 133, 132)

[sasori]

I think the FROM is not right in that one.

[LifeinAsia]

You might need to scope it (see below), but otherwise it should work:
UPDATE products SET
price2 = products.price,
price = products.price*0.75

What DB are you using?

[edited by: LifeinAsia at 9:40 pm (utc) on June 18, 2009]

[LifeinAsia]

Alternatively, this should work:
UPDATE products SET
price2 = price,
price = price*0.75
FROM products
WHERE pid IN
(SELECT pid
FROM products_categories
WHERE products_categories.cid not in (138, 128, 129, 130, 131, 133, 132))

[sasori]

its version 5.
I'll try your last one tomorrow. I wound up exporting a list based on a match of the cids and creating an update list to put the original settings of price/price2 back after changing all of them.