Hi Thanks for the test, I try it and could get it worked ... so I dig a little deeper and found that I had defined a unique index on data ! and doing something like concat(data, ' copy') in the insert query solved the problem !
Thanks a lot for helping ! François > -----Original Message----- > From: Kristen G. Thorson [mailto:[EMAIL PROTECTED] > Sent: mercredi, 14. décembre 2005 22:14 > To: RAPPAZ Francois > Cc: mysql@lists.mysql.com > Subject: Re: Is "select ... insert" working with 3.23(58) ?? > > RAPPAZ Francois wrote: > > >Hi > > > >I'm stuck with a sequence of sql commands to duplicate a > record whithin the same table (server is 3.23.58): > > > >I have table t1 with data : char and id: auto_increment, > int, unsigned, primary key. > >I would like to do the following > > > >SET @template = 104; > >DROP table IF EXISTS tmp; > > > >CREATE TABLE tmp > >SELECT data > >FROM t1 > >WHERE [EMAIL PROTECTED]; > > > >INSERT INTO t1 (id, data) > >SELECT null as id, tmp.data > >FROM tmp; > > > >And I always get 0 rows inserted. > > > >By the way last_insert_id() gives something meaningfull: the > id is incremented, but the other fields are left empty. > > > >I there a way to get this working with that server version ? > > > >Thanks for any help > > > >François Rappaz > > > > > > > > > Works for me on 3.23.58-log. Make sure @template is an id > that exists. > > > [EMAIL PROTECTED] root]# mysql test > Reading table information for completion of table and column > names You can turn off this feature to get a quicker startup with -A > > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 1382731 to server version: 3.23.58-log > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> CREATE TABLE `t1` ( `id` int(11) NOT NULL > auto_increment, `data` > char(3) NOT NULL default '', PRIMARY KEY (`id`) ); Query > OK, 0 rows affected (0.01 sec) > > mysql> INSERT INTO t1 SELECT id, code AS data FROM > mysql> VATtest.VAT_locations; > Query OK, 8 rows affected (0.00 sec) > Records: 8 Duplicates: 0 Warnings: 0 > > mysql> SELECT * FROM t1; > +----+------+ > | id | data | > +----+------+ > | 1 | 1 | > | 2 | 2 | > | 3 | 3 | > | 4 | 40 | > | 5 | 50 | > | 6 | 51 | > | 7 | 60 | > | 8 | 99 | > +----+------+ > 8 rows in set (0.00 sec) > > mysql> SET @template = 1; > Query OK, 0 rows affected (0.00 sec) > > mysql> DROP table IF EXISTS tmp; > Query OK, 0 rows affected (0.00 sec) > > mysql> CREATE TABLE tmp SELECT data FROM t1 WHERE [EMAIL PROTECTED]; > Query OK, 1 row affected (0.00 sec) > Records: 1 Duplicates: 0 Warnings: 0 > > mysql> INSERT INTO t1 (id, data) SELECT null as id, tmp.data FROM tmp; > Query OK, 1 row affected (0.00 sec) > Records: 1 Duplicates: 0 Warnings: 0 > > mysql> SELECT * FROM t1; > +----+------+ > | id | data | > +----+------+ > | 1 | 1 | > | 2 | 2 | > | 3 | 3 | > | 4 | 40 | > | 5 | 50 | > | 6 | 51 | > | 7 | 60 | > | 8 | 99 | > | 9 | 1 | > +----+------+ > 9 rows in set (0.00 sec) > > > > > kgt > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
