List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:August 12 2006 3:46am
Subject:Re: replace question...
View as plain text  
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
>
>
Thread
replace question...bruce12 Aug
  • Re: replace question...Dan Buettner12 Aug