insert into...select

I'm working with mysql 5.0.27 and I want to duplicate a row in the same
table... so i tried this query:

CREATE TABLE `test` (
`testId` int(10) NOT NULL auto_increment,
`testNameNl` varchar(150) NOT NULL default '',
`testNameFr` varchar(150) NOT NULL default '',
`testNameEn` varchar(150) NOT NULL default '',
`testTextNl` text NOT NULL,
`testTextFr` text NOT NULL,
`testTextEn` text NOT NULL,
`testMinimum` decimal(2,0) NOT NULL default '0',
`testOrder` tinyint(3) NOT NULL default '1',
`sspprofileId` smallint(5) unsigned NOT NULL default '0',
`testDate` int(14) NOT NULL default '0',
`projectId` int(10) NOT NULL default '0',
PRIMARY KEY (`testId`),
KEY `projectId` (`projectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;


INSERT INTO test
SELECT * FROM test AS testsource WHERE testsource.testId = '15'
ON DUPLICATE KEY UPDATE testId = 'newautoincrement?' , projectId = '8',
testOrder = '2'

I'm having 2 problems with this:

1. the database doens't duplicate the row, instead it just changes the
original row.
2. how can I determine the new autoincrement value, or let it update
automatically. Remember I do need the SELECT * , because the tables can
change sometimes (extra fields can be added).

Anyone a solution?

> INSERT INTO test
> SELECT * FROM test AS testsource WHERE testsource.testId = '15'
> ON DUPLICATE KEY UPDATE testId = 'newautoincrement?' , projectId = '8',
> testOrder = '2'
>
> I'm having 2 problems with this:
>
> 1. the database doens't duplicate the row, instead it just changes the
> original row.

This is exactly what the command does. If a uniquenes constraint is
violated, the existing row gets updated

> 2. how can I determine the new autoincrement value, or let it update
> automatically. Remember I do need the SELECT * , because the tables can
> change sometimes (extra fields can be added).

You can set the autoincrement column to NULL or just omit it.
>
> Anyone a solution?
>

INSERT <list all the fields, except for the testId> SELECT <the same
list> FROM testsource WHERE testId=15

This should do it.
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On 25 jan, 12:57, Willem Bogaerts
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> > INSERT INTO test
> > SELECT * FROM test AS testsource WHERE testsource.testId = '15'
> > ON DUPLICATE KEY UPDATE testId = 'newautoincrement?' , projectId = '8',
> > testOrder = '2'
>
> > I'm having 2 problems with this:
>
> > 1. the database doens't duplicate the row, instead it just changes the
> > original row.This is exactly what the command does. If a uniquenes constraint is
> violated, the existing row gets updated
>
> > 2. how can I determine the new autoincrement value, or let it update
> > automatically. Remember I do need the SELECT * , because the tables can
> > change sometimes (extra fields can be added).You can set the autoincrement column to NULL or just omit it.
>
>
>
> > Anyone a solution?INSERT <list all the fields, except for the testId> SELECT <the same
> list> FROM testsource WHERE testId=15
>
> This should do it.
> --
> Willem Bogaerts
>
> Application smith
> Kratz B.V.http://www.kratz.nl/

Thanks,

I thought it would update the new row instead of the original one... a
little bit confusing, but I solved it now.

On 25 Jan, 13:33, gehegerade...@gmail.com wrote:
> On 25 jan, 12:57, Willem Bogaerts
>
>
>
>
>
> <w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> > > INSERT INTO test
> > > SELECT * FROM test AS testsource WHERE testsource.testId = '15'
> > > ON DUPLICATE KEY UPDATE testId = 'newautoincrement?' , projectId = '8',
> > > testOrder = '2'
>
> > > I'm having 2 problems with this:
>
> > > 1. the database doens't duplicate the row, instead it just changes the
> > > original row.This is exactly what the command does. If a uniquenes constraint is
> > violated, the existing row gets updated
>
> > > 2. how can I determine the new autoincrement value, or let it update
> > > automatically. Remember I do need the SELECT * , because the tables can
> > > change sometimes (extra fields can be added).You can set the autoincrement column to NULL or just omit it.
>
> > > Anyone a solution?INSERT <list all the fields, except for the testId> SELECT <the same
> > list> FROM testsource WHERE testId=15
>
> > This should do it.
> > --
> > Willem Bogaerts
>
> > Application smith
> > Kratz B.V.http://www.kratz.nl/Thanks,
>
> I thought it would update the new row instead of the original one...
You did???
When I read in the manual:

"If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that
would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an
UPDATE of the old row is performed."

I took the statement "an UPDATE of the old row is performed" to mean
that the old row would be updated. How is such a plain statement
confusing?

> a
> little bit confusing, but I solved it now.- Hide quoted text -- Show quoted text -