[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