Oracle SQL update script

Two tables involved in this update script that I need.  ARINVT and TRANSLOG.  I need to update ARINVT.CYCLE_COUNT_DATE with the data from TRANSLOG.TRANS_DATE.  The conditions for this update is that TRANSLOG.TRANS_DESCRIP = 'CYC CT' and the date of TRANSLOG.TRANS_DATE must = systemdate.

Columns involved:

ARINVIT.ID (PK)
ARINVIT.CYCLE_COUNT_DATE (DATE TIME)

TRANSLOG.ARINVT_ID (FK)
TRANSLOG.TRANS_DATE (DATE TIME)
TRANSLOG.TRANS_DESCRIP (STRING)
-Darvin-Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Look at the MERGE sql statement:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

There is a straight forward example farther down in the link.  You just don't use the WHEN NOT MATCHED piece.
slightwv (䄆 Netminder) Commented:
>>ARINVT.CYCLE_COUNT_DATE with the data from TRANSLOG.TRANS_DATE.

I was going to try to mock up something that should be close but something doesn't make sense.

If you want to set CYCLE_COUNT_DATE   = TRANS_DATE and then you say: TRANS_DATE must = systemdate

Couldn't you just set CYCLE_COUNT_DATE = sysdate?
slightwv (䄆 Netminder) Commented:
This should be close:

merge into ARINVIT a
using (select arinvt_id, trans_date from translog where TRANSLOG.TRANS_DESCRIP = 'CYC CT' and TRANSLOG.TRANS_DATE >= trunc(sysdate) and TRANSLOG.TRANS_DATE < trunc(sysdate+1) ) t
on (a.id=t.arinvt_id)
when matched then update set a.cycle_count_date=t.trans_date
/

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

-Darvin-Author Commented:
More information.

The reason I say that TRANS_DATE must = systemdate is to select everything in translog that = todays date.  There will be many matches in translog where arinvt.id matches translog.arinvt_id.  So, I only want to match on records that are = to todays date.  Of those we only want to look at records that have the cycle count code of 'CYC CT'.

We are doing inventory transactions through the day that create one of these CYC CT transaction types but updating that CYCLE_COUNT_DATE field manually every time is too cumbersome.  So, ideally I would like the date and time the transaction actually happened to be put into CYCLE_COUNT_DATE.

If we really can't get that to work it is acceptable to just use sysdate but that could be much later than when it actually happened.

Does that many any better sense?  I'm trying to get the merge you suggested to work.  So far the script runs without error but isn't updating CYCLE_COUNT_DATE.

merge into arinvt A
using (select arinvt_id, trans_date, trans_descrip from translog where trans_descrip = 'CYC CT' and trans_date = sysdate) D
on (A.id = D.arinvt_id)
WHEN MATCHED THEN UPDATE SET A.cycle_count_date = D.trans_date

Open in new window

-Darvin-Author Commented:
Your statement looks like it is working.... doing a little more testing!
slightwv (䄆 Netminder) Commented:
>>The reason I say that TRANS_DATE must = systemdate is to select

but think about the logic:
select trans_date from translog where trans_date=sysdate and blah blah blah.

should return:
9/9/2015

Then you update arinvt.cycle_count_date  with that date.  Why not skip the select and just update arinvt.cycle_count_date  with sysdate?

I'm missing something.
-Darvin-Author Commented:
Because I want the time it occurred.  This script will run on it's own every day at say 11pm.  Earlier that day someone make a CYC CT transaction at 09:52:33.  If I don't take the date / time for that transaction and insert it into CYCLE_COUNT_DATE I just get 9/9/2015.  I'd like to know when if possible.  Again, if not possible getting the sysdate into that field is better than nothing.

So, your script works and does exactly what I want to do but I ran into a problem.  IF, there is more than one CYC CT transaction for that same ARINVT.ID in the same day it errors.

"Unable to get a stable set of rows in the source tables".

Any way around that?

In this case I would like the update to select the most recent CYC CT.
slightwv (䄆 Netminder) Commented:
>> IF, there is more than one CYC CT transaction for that same ARINVT.ID in the same day it errors.

I was wondering if you had that issue.

>>Any way around that?

Yep.  A couple of ways.

try this way.

change:
using (select arinvt_id, trans_date from translog where TRANSLOG.TRANS_DESCRIP = 'CYC CT' and TRANSLOG.TRANS_DATE >= trunc(sysdate) and TRANSLOG.TRANS_DATE < trunc(sysdate+1) ) t


to:
using (select arinvt_id, max(trans_date) trans_date from translog where TRANSLOG.TRANS_DESCRIP = 'CYC CT' and TRANSLOG.TRANS_DATE >= trunc(sysdate) and TRANSLOG.TRANS_DATE < trunc(sysdate+1) group by arinvt_id) t

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-Darvin-Author Commented:
PERFECT, thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.