SQL command to copy selected content from selected rows into other rows?

Any idea how to copy: name, content from rows where language_id = 1 to rows where language开发者_C百科_id = 2? How should SQL command look like?

I want to achive:

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html is what you need to do

assuming it is the productid that you want to update from lang1 to lang 2

update a set
a.name = b.name,
a.content = b.content
from tablea a 
join tablea b on a.productid = b.productid
where a.language_id = 2
and b.language_id = 1

ofcourse this will do it for every row in the table so if you want to restrict it then make sure to restrict it by the productids

Did you mean copying all language_id=1 rows to language_id=2 ones?

My knowledge of MySQL syntax is very poor, so I dare not give you all the codez, but at least you may find the following approach useful:

1. Create a temp table with the structure like this:

   product_id int,
   name (varchar?)
   content (varchar?)
   

   That is, include product_id and all the columns you need to copy.

2. Populate the temp table with the language_id=1 data. Probably like this:

   INSERT INTO temp_table
   SELECT product_id, name, content
   FROM orig_table
   WHERE language_id = 1
   

3. Update those rows in the original table where language_id=2 with the corresponding data in the temp table. It may look like this:

   UPDATE orig_table
   SET
     name = temp_table.name,
     content = temp_table.content
   FROM temp_table
   WHERE orig_table.product_id = temp_table.product_id
     AND orig_table.language_id = 2
   

4. Insert the rows from the temp table into the original table, where the products don't have language_id=2. Something like this:

   INSERT INTO orig_table (product_id, language_id, name, content)
   SELECT product_id, 2, name, content
   FROM temp_table
   WHERE NOT EXISTS (
     SELECT 1 FROM orig_table
     WHERE product_id = temp_table.product.id
       AND language_id = 2
   )
   

If you didn't mean to change the already existing language_id=2 data, then step #3 should be omitted and you might further want to modify step #2 in such a way that it selected language_id=1 data only for the products lacking language_id=2.