CODEWITHSUNDEEP
mysqlsql
Sackling
Sackling

Reputation: 1820

copy data to column when product id is the same

I need to copy over a product description specs (column name: product_description_second) of the same product but a different language id (both are in english just different countries).

So I have:

UPDATE products_description SET
    products_description_second = products_description_second
WHERE language_id=3 AND products_id = products_id

But this doesn't make sense because I'm not really identfying which description is copying to which but I only want to set the ones with language_id 3 to be the same as language id 1.

and also in my constraints I am setting product_id = product_id which doesnt make sense either I don't think

Upvotes: 1

Views: 53

Answers (2)

Emil Moise
Emil Moise

Reputation: 393

Perhaps this example will help you in making the difference between source and destination table, even if they are the same:

update 
    product_description dest_pd 
  set 
    dest_pd.product_description_second = (
      select 
          src_pd.product_description_second 
        from 
          product_description src_pd 
        where 
          src_pd.products_id = dest.products_id
          and src_pd.language_id = 3
  )

Upvotes: 0

Veljko89
Veljko89

Reputation: 1953

You just need to join table on itself

UPDATE pd1 SET
     pd1.products_description_second = pd2.products_description_second
from products_description pd1 
    join products_description pd2 on pd1.products_id = pd2.products_id 
                                and  pd2.language_id = 3
WHERE pd2.language_id = 1

something like this, do select before update to make sure it is what you asked for, it's how you do it in T-SQL

Upvotes: 1

Related Questions