Claudine, The syntax that I always refer back to for these sort of updates is this:
update Table2 set Id2 = T1.Id2 from Table1 T1, Table2 T2 where T1.Id1 = T2.Id1 I think it was Bill Downall who suggested it and it has always worked for me. The only thing that I would add about your particular problem is that I would have reversed the where and the and clauses: UPDATE TABLE + SET billing_amt = (t1.da_fee + t2.adjusted_total) + FROM d_b_allocation t1, d_invoice_header t2 + WHERE t1.da_invoice_dt = 12/20/2004 + AND t1.invoice_id = t2.invoice_id I seem to remember being taught to do the linking clause last but I can't think why it would make any difference. Also, I'm not sure if you can extract values from another table as you have attempted inside your brackets. I would have thought that the values might need to come from the first named table only. Regards, Alastair. ----- Original Message ----- From: "Claudine Robbins" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, December 20, 2004 7:29 AM Subject: [RBG7-L] - RE: UPDATE syntax > Javier, Larry, > > Neither putting quotes around the date nor removing the t1 from the first > field produced any results. > > I can browse records based on the date typed in as 12/20/04. > > I also tried creating a view first but same non-result. > > Anyway, it's late, huh, I mean early..... I'll try to tackle this a little > while later after I get some sleep. > > If anyone else has an idea what I can try, please let me know. > > Thanks, > > Claudine :) > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Javier > Valencia > Sent: Sunday, December 19, 2004 9:19 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - RE: UPDATE syntax > > Claudine: > If I remember correctly, the following has worked for me in the past: > UPDATE TABLE + > SET t1.billing_amt = (t1.da_fee + t2.adjusted_total) + > FROM d_b_allocation t1, d_invoice_header t2 + > WHERE t1.invoice_id = t2.invoice_id + > AND t1.da_invoice_dt = '12/20/2004' > I use quotes around the date and you may also need to use 2004 instead of 04 > depending on you date settings. One way to check if there are records > available is to run: > Browse * from d_b_allocation WHERE da_invoice_dt = 12/20/04 > And > Browse * from d_b_allocation WHERE da_invoice_dt = '12/20/2004' > And see if which statement returns records. > 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 Claudine > Robbins > Sent: Sunday, December 19, 2004 7:11 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - UPDATE syntax > > Need help please with an update command... > > UPDATE TABLE + > SET t1.billing_amt = (t1.da_fee + t2.adjusted_total) + > FROM d_b_allocation t1, d_invoice_header t2 + > WHERE t1.invoice_id = t2.invoice_id > > The statement above works fine but acts upon the whole table. I want to > limit the update to: > > AND t1.da_invoice_dt = 12/20/04 > > No matter where I put: AND t1.da_invoice_dt = 12/20/04, it says there are no > records at all to update. > > I even tried to put the UPDATE statement inside a cursor while loop and even > though it doesn't generate any errors, it doesn't update anything. > > Thanks for any and all suggestions :) >
