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]
>
>
>
