i've got an issue that i can't figure out... i'm trying to do a replace,
where i only replace information from one tbl, into the targeted tbl, if the
targeted tbl 'valid' item = 0;
if the tbls are:
data from external app (fromTBL):
foo1 = ['a1','b1','c1']
foo2 = ['a2','b2','c2']
foo3 = ['a3','b3','c3']
foo4 = ['a4','b4','c4']
foo5 = ['a5','b5','51']
data in the db/tbl (tgtTBL):
db1 = ['a1','b1','c1',1]
db2 = ['a2','b2','c2',0]
db3 = ['a5','b5','51',1]
foo represents the rows from the app. db represents the rows in the tbl.
so the foo rows may or may not be in the db. the idea is to be able to do a
'replace' for the foo rows and to replace those who have a valid==0, and to
insert any foo that isn't already in the tbl. the db/tbl is setup to
automatically set valid=0
so if there's a way to create a list of the foo lists, and then to do a
large replace using the executemany, then i could possibly do this in one
i've tried to do this:
rSQL = """replace into trgtTBL (cat, dog)
select t.cat, t.dog
from fromTBL t, trgtTBL
where trgtTBL.valid = 0"""
this approach should get all the rows of the fromTBL, and insert/replace
them into the trgtTBL where the 'valid' item is 0... or at least that's what
i'm trying to accomplish.
this however, seems to update the rows regardless of the value of 'valid'
there appears to be something subtle that i'm missing.
any help would be seriously appreciated.