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 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]
