yataaa wrote:
> Hi all,
>
> is there any way to do the following in SQL:
> Create a new row by copying an existing row, in such a way that certain
> values of the existing row have been changed in the newly created row?
> What I mean is "create a new row, use row X as template, but change the
> value in column 'name' to 'foobar'"
>
> I tried to do it via a derived table in which I update that column, but
> SQL alerted me that this is not possible:
> UPDATE
> (SELECT * FROM tablename WHERE ID=X) as a
> SET name=foobar
>
> There has to be some way to do this, but I just don't get it.

If you used an intermediary table, you could us INSERT...SELECT

http://dev.mysql.com/doc/refman/5.1/...rt-select.html

Copy the row(s) into your intermediary table and then copy them back
with the relevant changes