On Wed, Jul 9, 2008 at 9:37 AM, Phil <[EMAIL PROTECTED]> wrote:
> Is it possible to do an insert into with subselect and group by with an
> additional on duplicate insert ?
>
> CREATE TABLE NEW_TABLE (
> `a` varchar(10),
> `b` double
> ) engine=MyISAM;
>
>
> INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by
> old.a)
> on duplicate key
> update b=sum(old.y);
The following I should work for you...
mysql> CREATE TABLE NEW_TABLE (
-> `a` varchar(10),
-> `b` double
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.33 sec)
mysql> CREATE TABLE OLD_TABLE (x int,y int);
Query OK, 0 rows affected (0.36 sec)
mysql> ALTER TABLE NEW_TABLE ADD UNIQUE(`a`);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO OLD_TABLE VALUES(1,1),(1,1),(2,2);
Query OK, 3 rows affected (0.17 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO NEW_TABLE VALUES(2,3);
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO NEW_TABLE
-> SELECT x, sum(y)
-> FROM OLD_TABLE
-> GROUP BY X
-> ON DUPLICATE KEY UPDATE b = VALUES(b);
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 1 Warnings: 0
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
