List:General Discussion« Previous MessageNext Message »
From:RAPPAZ Francois Date:December 15 2005 8:05am
Subject:RE: Is "select ... insert" working with 3.23(58) ??
View as plain text  
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
> 
> 
> 
> 
> 
Thread
Is "select ... insert" working with 3.23(58) ??RAPPAZ Francois14 Dec
  • Re: Is "select ... insert" working with 3.23(58) ??Kristen G. Thorson14 Dec
RE: Is "select ... insert" working with 3.23(58) ??RAPPAZ Francois15 Dec