2

I have a ton of products in the products table. Each product has a product_specification. I need to update all the product_specification.document_type_id's to the value of '2'. Here's what I came up with while perusing SO:

update product_specifications
set product_specifications.document_type_id = 2
inner join products
on products.product_specification_id = product_specifications.id
where products.id in (
  select p.id from products as p
  inner join product_specifications as ps
  on p.product_specification_id = ps.id
  where p.store_front_id = 71 
  and ps.document_type_id = 1)

But this doesn't work...

cn007b
  • 16,596
  • 7
  • 59
  • 74
Mike Glaz
  • 5,352
  • 8
  • 46
  • 73

2 Answers2

1

I don't see the benefit from using two queries since you are joining the same tables.. You can use JOIN together with UPDATE (see answer here)

UPDATE product_specifications ps INNER JOIN products p
ON ps.id=p.product_specification_id
SET ps.document_type_id = 2
WHERE p.store_front_id = 71 AND ps.document_type_id = 1

EDIT: Some more info

As you know the UPDATE syntax (docs here) for both single and multiple tables is like:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET ...`

and as stated few rows below in the docs

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions.

Here is the doc about table_references

Community
  • 1
  • 1
Yuri
  • 3,082
  • 3
  • 28
  • 47
0

Your query little bit incorrect in my opinion, and i guess you should obtain syntax error, because you should follow syntax:

update tableName join anotherTableName set ... where ...

and i think your query should looks like:

update product_specifications
inner join products
on products.product_specification_id = product_specifications.id
set product_specifications.document_type_id = 2
where products.id in (
  select p.id from products as p
  inner join product_specifications as ps
  on p.product_specification_id = ps.id
  where p.store_front_id = 71 and ps.document_type_id = 1
)

I didn't check this query, but i wrote the same queries lot of times, and i truly believe it'll help you...

cn007b
  • 16,596
  • 7
  • 59
  • 74
  • 1
    Why should the OP "try this"? A **good answer** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. – RiggsFolly Apr 13 '16 at 15:37