How to copy row with updated value

[Jsama]

Hello hi,

just a quick question: is there any sql that i can copy a row in mysql and update some values in it?

For example, i have a table like this.

Name | School | Year
John |  ABC School | 2009
Mary | CDE School | 2010

And i want to insert a new row with the same Name and School but just updated Year, e.g.

Name | School | Year
Mary | CDE School | 2010
John |  ABC School | 2009
Mary | CDE School | 2010

Thanks a lot

Regards,
Jsama

[dlite922]

In your example you just copied the year too? didn't you mean to change that from 2009 to 2010?

If so, then this should work:

INSERT INTO foo (Name, School, Year) (SELECT Name, School, 2010 AS Year FROM foo WHERE Year = '2009');

That will update all 2009 schools to 2010. You can remove the where clause to copy everything. Or do something like WHERE year != 2010.

Good luck,



Dan