Thanks Bill I think I will go with that approach.
I knew that it could be done with a view/tab but I was trying to be too smart for my own good and do it with one command. Marc. ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Monday, August 22, 2005 11:20 AM Subject: [RBG7-L] - RE: update command > Marc, > > I don't believe the multi-table UPDATE syntax allows a sub-query to the > right of the equal sign after SET <columnname>. > > You could create a view of your COUNT... GROUP BY subquery, and then > simplimy greatly your update command with the update target table and > the view. I'm not sure I understand the logic, so I might have this > wrong, but it would be something like this: > > CREATE TEMP VIEW TranDateCounts + > (CustNum, BldDate, DateCount) + > AS + > SELECT + > CustNum, BldDate, COUNT (DISTINCT tr_date) + > FROM Tran_hist + > GROUP BY CustNum, BldDate > > UPDATE InsLogB + > SET OvperClm = DateCount + > FROM InsLogB i1, TranDateCounts t2 + > WHERE (i1.custnum = t2.custnum) + > AND (i1.billDate = t2.blddate) > > Bill > > Albert Berry wrote: > > Another typo perhaps? You have "inslogb t1 <period> tran_hist t2" instead of "inslogb t1 <comma> > > tran_hist t2" > > > > --- Javier Valencia <[EMAIL PROTECTED]> wrote: > > > > > >>Marc: > >> > >>Update inslogb set OvperClm = select (count(distinct tr_date)) + > >>from tran_hist where tr_type = 1 group by custnum,blddate + > >>From inslogb T1. tran_hist T2 where T1.custnum = T2.custnum + > >>and T1.billdate = T2.blddate > >><<<<< > >>A quick look indicates that you have a period (.) too many after T1 on the > >>third line... > >>Javier, > >> > >>Javier Valencia, PE > >>President > >>Valencia Technology Group, L.L.C. > >>14315 S. Twilight Ln, Suite #14 > >>Olathe, Kansas 66062-4578 > >>Office (913)829-0888 > >>Fax (913)649-2904 > >>Cell (913)915-3137 > >>================================================ > >>Attention: > >>The information contained in this message and or attachments is intended > >>only for the person or entity to which it is addressed and may contain > >>confidential and/or privileged material. Any review, retransmission, > >>dissemination or other use of, or taking of any action in reliance upon, > >>this information by persons or entities other than the intended recipient > >>is prohibited. If you received this in error, please contact the sender and > >>delete the material from all system and destroy all copies. > >>====================================================== > >> > >>-----Original Message----- > >>From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Marc > >>Sent: Monday, August 22, 2005 9:11 AM > >>To: RBG7-L Mailing List > >>Subject: [RBG7-L] - update command > >> > >>Hi all > >> > >>I am having trouble with this update command. > >>It looks like it should work but I can't see what > >>is wrong. > >> > >>I know I could do this with a temp tab or view but > >>want to try to do it in one command. > >> > >>Update inslogb set OvperClm = select (count(distinct tr_date)) + > >>from tran_hist where tr_type = 1 group by custnum,blddate + > >>From inslogb T1. tran_hist T2 where T1.custnum = T2.custnum + > >>and T1.billdate = T2.blddate > >> > >>I also tried moving the ( ) around the select statement, the select > >>statement works by itself. > >> > >>thanks for any help > >>Marc > >> > >> > > > > > > > > Albert Berry > > Management Consultant > > RR2 - 1252 Ponderosa Drive > > Sparwood BC, V0B 2G2 > > Canada > > (250) 425-5806 > > (250) 425-7259 > > (708) 575-3952 (fax) > > [EMAIL PROTECTED] > > > > > > > >
