Ok, here's what I've done so far... maybe someone could find this useful :)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
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'
'0000000','120','PCS27LI','PAR37UO','4'
'0000000','122','PCS27LI','CEN47UO','2'
'0000000','123','PCS27LI','GDV57UO','6'
'0006429','123','BELL7CI','GDV57UO','3'
'0000000','110','PCS27LI','PAR37UO','3'
'0000287','120','BELL7CI','PAR37UO','13'
'0000287','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]
