[thelist] SQL; INSERT or UPDATE SELECT, SOLVED
Jay Blanchard
jay.blanchard at niicommunications.com
Thu May 16 11:49:01 CDT 2002
[snip]
It would be nice to do something like this;
mysql> Insert into tblCollect(tc.over30,
-> tc.over60,
-> tc.over90,
-> tc.over120)
-> select tct.over30,
-> tct.over60,
-> tct.over90,
-> tct.over120
-> from tblCollectTemp tct, tblCollect tc
-> where tct.btn = tc.btn;
ERROR 1093: INSERT TABLE 'tblCollect' isn't allowed in FROM table list
[/snip]
*dagnabit, sometimes the smallest things will get you*
How about a REPLACE?
mysql> replace tblCollect(btn, over30, over60, over90, over120)
-> select btn, over30, over60, over90, over120
-> from tblCollectTemp;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 4 Warnings: 0
mysql> select * from tblCollect;
+----+------------+--------+--------+--------+---------+
| id | btn | over30 | over60 | over90 | over120 |
+----+------------+--------+--------+--------+---------+
| 34 | 2105551000 | 30.12 | 18.76 | 112.56 | 87.94 |
| 35 | 2105551001 | 12.35 | 6.64 | NULL | NULL |
| 37 | 2105558001 | 12.17 | 5.56 | NULL | 100.00 |
| 33 | 2105553001 | 17.01 | NULL | 32.46 | NULL |
| 36 | 2105551002 | 12.31 | 56.12 | NULL | NULL |
+----+------------+--------+--------+--------+---------+
You MUST have a UNIQUE KEY (btn in this example) in the table being updated
for this to work. A problem
could be scalability.
tata!
Jay
More information about the thelist
mailing list