[thelist] SQL; INSERT or UPDATE SELECT

Jay Blanchard jay.blanchard at niicommunications.com
Thu May 16 11:06:01 CDT 2002


Greetings Group!

I have two tables, one where where we get new values for a DB and a table
that needs to be updated with these values. I can do this via scripting, but
as always I am looking for a one query way (with limitations imposed by
MySQL) to do it if possible. Here are the tables;

mysql> select * from tblCollect; (the table to be updated)
+----+------------+--------+--------+--------+---------+
| id | btn        | over30 | over60 | over90 | over120 |
+----+------------+--------+--------+--------+---------+
|  1 | 2105551000 |  30.12 |  18.76 | 112.56 |   87.94 |
|  2 | 2105551001 |  12.35 |   NULL |   NULL |    NULL |
|  3 | 2105551002 |  12.31 |  56.12 |   NULL |    NULL |
|  4 | 2105558001 |  12.17 |   5.56 |   NULL |    NULL |
+----+------------+--------+--------+--------+---------+

mysql> select * from tblCollectTemp; (the table that receives the updated
information)
+----+------------+--------+--------+--------+---------+
| id | btn        | over30 | over60 | over90 | over120 |
+----+------------+--------+--------+--------+---------+
|  1 | 2105551000 |  30.12 |  18.76 | 112.56 |   87.94 |
|  2 | 2105551001 |  12.35 |   6.64 |   NULL |    NULL |
|  3 | 2105551002 |  12.31 |  56.12 |   NULL |    NULL |
|  4 | 2105558001 |  12.17 |   5.56 |   NULL |    NULL |
+----+------------+--------+--------+--------+---------+

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

And of course there is nothing like UPDATE foo SET bar=(select statement).

Can it be done?

THANKS!

Jay





More information about the thelist mailing list