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.
--
kind regards, Dan

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

Dan Rumney wrote:
[snip]
>>
>> 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

Correction.

The documentation contradicts itself as to whether you can use
INSERT...SELECT to INSERT into the same table that you're SELECTing from.

Read the documentation and try it out!

Good luck

On 12 Jun, 00:10, yataaa <gates...@gmail.com> 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.
> --
> kind regards, Dan

You would do it like this:
Suppose you have a table with 3 fields: field_1, field_2, field_3 and
field_1 is the PK.
You want to copy a record where field_1 = 5, field_2 = 'Fred', field_3
= 'Bloggs' such that the new record has values field_1 = 8, field_2 =
'Fred', field_3 = 'Bloggs'

INSERT INTO table
SELECT
8,
field_2,
field_3
FROM table
WHERE field_1 = 5

Captain Paralytic wrote:

> INSERT INTO table
> SELECT
> 8,
> field_2,
> field_3
> FROM table
> WHERE field_1 = 5

Ah, Eureka!

Alas.. SQL reports that the ID of the copied row is used twice - I did
not follow your example to set the ID and leave the rest. Is there any
way to let SQL decide what ID to use next?

I know I can work around that problem by selecting MAX(id) and
incrementing it by one, but it does not seem to me like a "nice" solution.

--
Dan

yataaa wrote:
> Captain Paralytic wrote:
>
>> INSERT INTO table
>> SELECT
>> 8,
>> field_2,
>> field_3
>> FROM table
>> WHERE field_1 = 5
>
> Ah, Eureka!
>
> Alas.. SQL reports that the ID of the copied row is used twice - I did
> not follow your example to set the ID and leave the rest. Is there any
> way to let SQL decide what ID to use next?
>
> I know I can work around that problem by selecting MAX(id) and
> incrementing it by one, but it does not seem to me like a "nice" solution.

Trying to assign a unique ID has all kinds of consequences (race
conditions etc.).

If your ID as an auto-incrementing field, you can either leave the ID
field out of it:
TABLE:
id
field1
field2

INSERT INTO table (field1,field2) SELECT field1,field2 FROM table;

Or, as with inserts, you can assign a NULL:
INSERT INTO table (id, field1,field2)
SELECT null,field1,field2 FROM table;
Or
INSERT INTO table SELECT null,field1,field2 FROM table;

.... although it's always better to name ones fields explicitly. It is a
little more work earlier one, which will save you a lot of headaches /
bughunts later on if you happen to change the tables.
--
Rik Wasmus
....spamrun finished