CODEWITHSUNDEEP
mysqlsqlsql-update
Ivan Dimov
Ivan Dimov

Reputation: 3

Mysql update column with value if id is in another table

I have two tables, one with products, one with products assocaited to categories, both tables looking like:

CREATE TABLE `oclh_product` (
  `product_id` int(11) NOT NULL,
  `mpn` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `oclh_product_to_category` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I need to update oclh_product.mpn from table oclh_product if product_id in oclh_product_to_category is equal to some number, to 26 for example.

I trying sql queries like this:

UPDATE `oclh_product` INNER JOIN `oclh_product_to_category`
    ON oclh_product_to_category.category_id = oclh_product.product_id
SET oclh_product.mpn = '99999' WHERE `oclh_product_to_category`.`category_id` = 26;

and like this:

UPDATE `oclh_product` INNER JOIN `oclh_product_to_category`
    ON oclh_product_to_category.category_id = '26'
SET oclh_product.mpn = '99999';

without success. Any tips for this UPDATE statement?

Upvotes: 0

Views: 45

Answers (2)

Ivan Dimov
Ivan Dimov

Reputation: 3

I found error, the right query that works is:

UPDATE `oclh_product` INNER JOIN `oclh_product_to_category`
    ON oclh_product_to_category.product_id = oclh_product.product_id
SET oclh_product.mpn = '99999' WHERE `oclh_product_to_category`.`category_id` = 26;

Thnak you again, Gordon Linoff.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your first query should work if you use the right JOIN conditions. TRable aliases make it easier to follow:

UPDATE oclh_product p INNER JOIN 
       oclh_product_to_category pc
       ON pc.product_id = p.product_id
    SET p.mpn = '99999'
WHERE pc.category_id = 26;

If you are storing a number in mpn, why is the type a string? It is better to use a number type of some sort.

Upvotes: 1

Related Questions