Héctor Villafuerte D. wrote:
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'
.....
Hi guys! Here's the Python script I wrote for an ADDITIVE UPDATE. I'm learning alot lately... maybe this can help someone out there. Hector
--------------------------------------------------------------------------------------------------- # Operators Traffic Report (otr) # Hector Villafuerte D. # 20031106 # # otr.py : v.0.1 : Prepares the monthly Operators Traffic Report
import MySQLdb, string, os
db = MySQLdb.connect(host = "localhost", user = "villaf", passwd = "secret", db = "otr")
cursor = db.cursor()
def update_hist(csv):
# Loads new data
cursor.execute("""TRUNCATE TABLE otr_new""")
cursor.execute("""ALTER TABLE otr_new DISABLE KEYS""")
cursor.execute("""LOAD DATA INFILE '%s' INTO TABLE otr_new FIELDS TERMINATED BY ',' """
"""ENCLOSED BY '\"' IGNORE 1 LINES""" % (csv))
cursor.execute("""ALTER TABLE otr_new ENABLE KEYS""")
# Intersection between *otr_hist* & *otr_new*
cursor.execute("""DROP TABLE IF EXISTS otr_tmp1""")
cursor.execute("create table otr_tmp1 "
"select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos + b.minutos as minutos "
"from otr_hist as a join otr_new as b using(tel, telefb, rutaentran, rutasalien)")
cursor.execute("""ALTER TABLE otr_tmp1 ADD INDEX (tel, telefb, rutaentran, rutasalien, minutos)""")
# Difference between *otr_new* & *otr_hist*
cursor.execute("""DROP TABLE IF EXISTS otr_tmp2""")
cursor.execute("create table otr_tmp2 "
"select a.* from otr_new as a left join otr_hist as b using(tel, telefb, rutaentran, rutasalien) "
"where b.minutos is null")
# Updates *otr_hist* with *otr_tmp1*
cursor.execute("UPDATE otr_hist AS A, otr_tmp1 AS B "
"SET A.minutos = B.minutos WHERE "
"A.tel = B.tel AND A.telefb = B.telefb AND "
"A.rutaentran = B.rutaentran AND A.rutasalien = B.rutasalien")
# Updates *otr_hist* with *otr_tmp2*
cursor.execute("""ALTER TABLE otr_hist DISABLE KEYS""")
cursor.execute("""INSERT INTO otr_hist SELECT * FROM otr_tmp2""")
cursor.execute("""ALTER TABLE otr_hist ENABLE KEYS""")update_hist("c:\\\\tmp\\\\20031002_03.csv")
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
