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:kthorson@stripped]
> Sent: mercredi, 14. décembre 2005 22:14
> To: RAPPAZ Francois
> Cc: mysql@stripped
> 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 id=@template;
> >
> >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.
>
>
> [root@linux2 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 id=@template;
> 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
>
>
>
>
>
