Héctor Villafuerte D. wrote:
Hi all!
I need to perform what I've called an additive UPDATE.
The logic is the next:
(1) There's a historic table (HISTORY) with two fields:
mysql create table history (ID char(7) primary key, VAL int(12));
(2) There's a new table everyday (TODAY) with exactly the
same structure as HISTORY (ID and VAL).
(3) I need to feed HISTORY with the values found in TODAY
in an additive way. I think that the pseudocode would be
like this:
* IF TODAY.id EXISTS IN HISTORY.id
- THEN UPDATE HISTORY.val = HISTORY.val + TODAY.val
* ELSE UPDATE HISTORY.id = TODAY.id, HISTORY.val = TODAY.val
... you see now why I called it an additive UPDATE? :)
Is there a way to perform this with just MySQL or do I need
to combine it with a programming language?
Thanks in advance,
Hector
Ok, here's what I've done so far... maybe someone could find this useful :)
The magic is in understanding JOIN's! The main reason why I installed
MySQL 4.1.0-alpha
was because of sub-selects (since I had no idea they where special cases
of JOIN's and
they are slower than JOIN's too!)
So, I'll try to use REPLACE later (so I don't have to query the last
UNION SELECT).
Of course, any comments are welcome!
Hector
# [mysql_localhost] Query Window
# Connection: mysql_localhost
# Host: localhost
# Saved: 2003-11-05 11:45:25
#
# Query:
# select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos +
b.minutos as total
# from grp_oper_hist as a join grp_oper_hoy as b using(tel, telefb,
rutaentran, rutasalien)
# union
# select a.* from grp_oper_hoy as a left join grp_oper_hist as b
using(tel, telefb, rutaentran, rutasalien)where b.minutos is null
# union
# select a.* from grp_oper_hist as a left join grp_oper_hoy as b
using(tel, telefb, rutaentran, rutasalien)where b.minutos is null
#
'tel','telefb','rutaentran','rutasalien','total'
'000','120','PCS27LI','PAR37UO','4'
'000','122','PCS27LI','CEN47UO','2'
'000','123','PCS27LI','GDV57UO','6'
'0006429','123','BELL7CI','GDV57UO','3'
'000','110','PCS27LI','PAR37UO','3'
'287','120','BELL7CI','PAR37UO','13'
'287','123','BELL7CI','GDV57UO','2'
'0002407','123','PCS27LI','GDV57UO','3'
'0003076','123','BELL7CI','GDV57UO','2'
'0006429','123','PCS27LI','GDV57UO','1'
'0009210','122','BELL7CI','CEN47UO','1'
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
