[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