CODEWITHSUNDEEP
mysqlsql
Sackling
Sackling

Reputation: 1820

update statement giving uknown column error

I am trying to update 1 column based on if the categories_id is equal to 90 in a second table but getting an unknown column error.

Here is my sql: 

UPDATE products SET qty='20' 
WHERE products.products_id = products_to_categories.products_id AND products_to_categories.categories_id = '90'

The error I am getting is

Unknown column 'products_to_categories.products_id' in 'where clause'

Upvotes: 2

Views: 55

Answers (5)

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

UPDATE products p, products_to_categories pc
SET p.qty='20' 
WHERE p.products_id = pc.products_id AND pc.categories_id = '90'

FIDDLE

Upvotes: 0

Bohemian
Bohemian

Reputation: 425238

You can use mysql's multiple table syntax:

UPDATE products
JOIN products_to_categories
  ON products.products_id = products_to_categories.products_id
  AND products_to_categories.categories_id = '90'
SET qty='20'

See this query syntax checked on SQLFddile.

Upvotes: 0

John Woo
John Woo

Reputation: 263843

you need to include the table in your join statement, products_to_categories.products_id can't be found unless the table is joined.

UPDATE products a
       INNER JOIN products_to_categories b
             ON a.products_id = b.products_id
SET    a.qty = '20' 
WHERE  b.categories_id = '90'

Upvotes: 0

Itay
Itay

Reputation: 16785

You can't combine two tables in an UPDATE command like that. Use a subquery.

UPDATE products SET qty='20' 
WHERE products.products_id IN (SELECT `ptc`.`products_id`
                               FROM   `products_to_categories` `ptc`
                               WHERE  `ptc`.categories_id = '90')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

In SQL, you have to introduce table names with from (or update or delete) statements. You may mean:

UPDATE products
    SET qty = '20' 
    WHERE exists (select 1
                  from products_to_categories ptc
                  where ptc.products_id = products.products_id AND
                        ptc.categories_id = '90'
                 );

Another way to do this is with a join:

UPDATE products p join
       products_to_categories ptc
       on  ptc.products_id = products.products_id AND
           ptc.categories_id = '90'
    SET p.qty = '20';

This works better if there is more than one match.

Upvotes: 1

Related Questions