Oracle SQL update script

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.

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

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

Select allOpen in new window

Your statement looks like it is working.... doing a little more testing!

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

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.

PERFECT, thanks!!