Confused about SQL update

Posted in 1999

Ian Briscoe — Thu, 18 Nov 1999 21:52:01 +0000 — source: Informix-list mailing list archive (1991-1998)

When I issue the following query: select count(*) from customer where compno =1 and ledger_code = "A" from customer where custno not in (select custno from customer_debt) a count of 514 is retrieved. But when I issue update customer set credit_flag = 0 where compno = 1 and ledger_code = "A" and custno not in (select custno from customer_debt) only 129 rows are updates. This is driving me crazy - any ideas??? Ian Briscoe

↪ replying to Ian Briscoe

mars1972@my-deja.com — Fri, 19 Nov 1999 20:22:25 GMT — source: Informix-list mailing list archive (1991-1998)

In article <38347501.89F48A3C@flashbang.demon.co.uk>, Ian Briscoe <ian@flashbang.demon.co.uk> wrote: > When I issue the following query: > select count(*) from customer where compno =1 and ledger_code = "A" > from customer where custno not in (select custno from customer_debt)> > a count of 514 is retrieved. > > But when I issue > update customer set credit_flag = 0 where compno = 1 and > ledger_code = "A" and custno not in > (select custno from customer_debt)> > only 129 rows are updates. This is driving me crazy - any ideas??? > > Ian Briscoe > > Hmmm, I know this is probably a stupid question, but it could be important. Are you sure you don't mean: select count(*) from customer where compno =1 and ledger_code = "A" AND custno not in (select custno from customer_debt) ? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to mars1972@my-deja.com

Ian Briscoe — Sat, 20 Nov 1999 09:56:59 +0000 — source: Informix-list mailing list archive (1991-1998)

Sorry, That's what I DID have!!! I accidentally duplicated the 'from customer' bit in my post!!! Incidentally, if I first SELECT each custno into a temporary table and then UPDATE based on the temp table - all works fine - ie 514 rows updated. mars1972@my-deja.com wrote: > In article <38347501.89F48A3C@flashbang.demon.co.uk>, > Ian Briscoe <ian@flashbang.demon.co.uk> wrote: > > When I issue the following query: > > select count(*) from customer where compno =1 and ledger_code = "A" > > from customer where custno not in (select custno from customer_debt)> > > > a count of 514 is retrieved. > > > > But when I issue > > update customer set credit_flag = 0 where compno = 1 and > > ledger_code = "A" and custno not in > > (select custno from customer_debt)> > > > only 129 rows are updates. This is driving me crazy - any ideas??? > > > > Ian Briscoe > > > > > > Hmmm, I know this is probably a stupid question, but it could be > important. > > Are you sure you don't mean: > > select count(*) from customer where compno =1 and ledger_code = "A" > AND custno not in (select custno from customer_debt)> > ? > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.