Greetings,
This seems like a simple task:
insert into prizes select * from prizes where prize_id=16;
but it gives an error because the prize_id column is autoincrement.
this fails as well
insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
strangely changing the prize_id from 16 to 17 rather than inserting
17.

I can create a temporary table with a single row, alter the prize_id
column in that table and insert the row back. I have also seen
solutions where the autoincrement is disabled and reenabled using
'modify table' commands, but that could get ugly IMHO.

In reality it takes 4 steps to accomplish my goal because I can alter
the data I want to change on the new row in the same command as
altering the prize_id. Optimally it would take 3 (duplicate, get
last_insert_id(), and update), and performance isn't an issue with
this task, but it bugs me to be so round-about.

suggestions?
-- clh

>insert into prizes select * from prizes where prize_id=16;
>but it gives an error because the prize_id column is autoincrement.
>this fails as well
>insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
>KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
>strangely changing the prize_id from 16 to 17 rather than inserting
>17.

Why is this strange? You've got a duplicate key (prize_id).

How about this, ***uming that your prizes table has fields prize_id,
nerk, jello, and noodle?

insert into prizes (prize_id, nerk, jello, noodle)
select null, nerk, jello, noodle from prizes where prize_id = 16;

Gordon Burditt wrote:
>> insert into prizes select * from prizes where prize_id=16;
>> but it gives an error because the prize_id column is autoincrement.
>> this fails as well
>> insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
>> KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
>> strangely changing the prize_id from 16 to 17 rather than inserting
>> 17.
>
> Why is this strange? You've got a duplicate key (prize_id).
>
> How about this, ***uming that your prizes table has fields prize_id,
> nerk, jello, and noodle?
>
> insert into prizes (prize_id, nerk, jello, noodle)
> select null, nerk, jello, noodle from prizes where prize_id = 16;
>
>

Or, just leave the prize_id column out all together:

insert into prizes (nerk, jello, noodle)
select nerk, jello, noodle from prizes where prize_id = 16;

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

On May 19, 5:48 pm, gordonb.9z...@burditt.org (Gordon Burditt) wrote:
> >insert into prizes select * from prizes where prize_id=16;
> >but it gives an error because the prize_id column is autoincrement.
> >this fails as well
> >insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
> >KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
> >strangely changing the prize_id from 16 to 17 rather than inserting
> >17.
>
> Why is this strange? You've got a duplicate key (prize_id).

it is strange because the insert is never done. the docs read like
the "on duplicate key update" updates the inserted row to avoid the
error which is what I expected.


> How about this, ***uming that your prizes table has fields prize_id,
> nerk, jello, and noodle?
>
> insert into prizes (prize_id, nerk, jello, noodle)
> select null, nerk, jello, noodle from prizes where prize_id = 16;

I need a generic solution so the code does not need to be altered when
the table changes.

>> >insert into prizes select * from prizes where prize_id=16;
>> >but it gives an error because the prize_id column is autoincrement.
>> >this fails as well
>> >insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
>> >KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
>> >strangely changing the prize_id from 16 to 17 rather than inserting
>> >17.
>>
>> Why is this strange? You've got a duplicate key (prize_id).
>
>it is strange because the insert is never done.

If you have a duplicate key, the insert is not done. This is what
unique indexes are for - preventing the insertion (or other creation)
of duplicates.

>the docs read like
>the "on duplicate key update" updates the inserted row to avoid the
>error which is what I expected.

No, the update acts on the CONFLICTING row. There is no inserted
row in this situation. It is unclear what will happen if there are
multiple conflicting rows because there are multiple unique keys
and multiple conflicts in the inserted records. I ***ume it would
pick one of the conflicting rows, but I don't know which. Or it
might do all of them.

One use for this (there's a unique index on url, not count):

insert into hit_counter(url, count) values($url, 1)
on duplicate key update count=count+1;

This lets you do a "fire and forget" query to increment a counter whether
a record for the given URL previously exists or not.

>>
>> insert into prizes (prize_id, nerk, jello, noodle)
>> select null, nerk, jello, noodle from prizes where prize_id = 16;
>
>I need a generic solution so the code does not need to be altered when
>the table changes.

I don't think you're going to get one that doesn't involve constructing
SQL on the fly after getting a list of field names.

> If you have a duplicate key, the insert is not done. This is what
> unique indexes are for - preventing the insertion (or other creation)
> of duplicates.
>

thanx --
I think my misunderstanding was because I was expecting the docs to he
helpful hehe
there are two entries that reference "ON DUPLICATE":
http://dev.mysql.com/doc/refman/5.0/...rt-select.html
and
http://dev.mysql.com/doc/refman/5.0/...duplicate.html
of course I was using the first one and only the second one specifies
the command acts like an "UPDATE".
Sigh.

>
> No, the update acts on the CONFLICTING row. There is no inserted
> row in this situation. It is unclear what will happen if there are
> multiple conflicting rows because there are multiple unique keys
> and multiple conflicts in the inserted records. I ***ume it would
> pick one of the conflicting rows, but I don't know which. Or it
> might do all of them.

the second entry in the docs addresses this situation, if you are
interested

>
> I don't think you're going to get one that doesn't involve constructing
> SQL on the fly after getting a list of field names.

Yeah -- that's more complex than creating the temporary table,
changing the autoincrement column, and inserting it back.

Thanx for your input!