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.