List:General Discussion« Previous MessageNext Message »
From:bruce Date:August 12 2006 12:36am
Subject:replace question...
View as plain text  
hi...

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:

fromTBL
  cat
  dog
  mouse

tgtTBL
  cat
  dog
  mouse
  valid


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
step...

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.

thanks

-bruce

Thread
replace question...bruce12 Aug
  • Re: replace question...Dan Buettner12 Aug