copy data from one field to another mysql mulitple rows

I want to copy data from one field to another mysql mulitple rows.

I've tried following but its not working for all and mysql even says开发者_如何转开发 "returns more than 1 row"

UPDATE agreement 
SET _date2 = (SELECT Concat(SUBSTRING(_date, 7), '-', SUBSTRING(_date, 4, 2), 
    '-', SUBSTRING(_date, 1, 2)) AS newdd FROM   agreementtemp); 

try

update `table_name` set  destination_field=source_field

Try

update `tableName` set col1=col2

For this to work both column should belong to same table and should be of same type.

You can update one field from another like this:

update mytable set field1=field2;

1. If you want to update multiple tables and you can join on that table you can use the join syntax also in the UPDATE

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

or

UPDATE TABLE_1
LEFT JOIN TABLE_2
ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2 
SET TABLE_1.COLUMN = EXPR
WHERE TABLE_2.COLUMN2 IS NULL

In your case it would be

UPDATE agreement a1 
JOIN agreementtemp a2 
ON a1.id = a2.id 
SET a1._date2 = Concat(SUBSTRING(a2._date, 7), '-', SUBSTRING(a2._date, 4, 2) , '-', SUBSTRING(a2._date, 1, 2));