Bruce, looks like you're missing join criteria, and so you're
selecting a Cartesian product into your trgtTBL ... this this on for
size:
replace into trgtTBL (cat, dog)
select t.cat, t.dog
from fromTBL t, trgtTBL
where trgtTBL.valid = 0
AND t.cat = trgtTBL.cat AND t.dog = trgtTBL.dog
Dan
On 8/11/06, bruce <bedouglas@stripped> wrote:
> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
