I have an external table that reads from a csv file. I then need to merge any updates or new rows to a table. The problem is the table does not have a unique identifier. I have account numbers and dates, but the date may get updated on an account number that may have multiple entries. For example:
MERGE INTO table p
USING(
SELECT DISTINCT account_name
, account_no
, amount
, bank_date
, date_applied
, update_date
FROM ext_table
WHERE update_date = to_date(sysdate, 'dd-Mon-yyyy') e
ON (p.account_no = e.account_no)
WHEN MATCHED THEN
UPDATE
SET p.account_name = e.account_name
, p.amount = e.amount
, p.bank_date = e.bank_date
, p.date_applied = e.date_applied
, p.update_date = e.update_date
;
The problem is that I am not able to just match the rows on the account numbers in the table with the rows in the external table that have an update date, because I have duplicate account numbers. Therefore, when I execute the merge statement all rows that match the account number will get updated, not just the rows that have been updated in the csv file(external table). All columns, but the account number has the potential to be updated. Is there another way to do this?
You need a 'stable' set of rows, ie one row for each join because otherwise the results of the merge would be dependent on which rows we processed first, which makes the whole thing "random".
SQL> drop table t purge;
Table dropped.
SQL> drop table src purge;
Table dropped.
SQL>
SQL> create table src ( acct int, upd date, z int );
Table created.
SQL>
SQL> insert into src values (1, date '2016-01-01', 1 );
1 row created.
SQL> insert into src values (1, date '2016-01-02', 2 );
1 row created.
SQL> insert into src values (10, date '2016-01-01', 11 );
1 row created.
SQL> insert into src values (11, date '2016-01-02', 12 );
1 row created.
SQL> insert into src values (12, date '2016-01-02', 12 );
1 row created.
SQL>
SQL> create table t ( tacct int primary key, tupd date, tz int );
Table created.
SQL>
SQL> insert into t values (1, date '2015-01-01', 1 );
1 row created.
SQL> insert into t values (10, date '2015-01-01', 11 );
1 row created.
SQL> insert into t values (11, date '2015-01-02', 12 );
1 row created.
SQL>
SQL>
SQL> merge into t
2 using
3 ( select distinct acct, upd, z from src ) s
4 on ( t.tacct = s.acct )
5 when matched
6 then update set tz = z, tupd = upd
7 /
merge into t
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
So we need an inner query that gets the data we need for the merge consistently. Here's an *example* where we pick up the latest data per account , where "latest" is defined by updated date.
SQL>
SQL> select acct, max(upd), max(z) keep ( dense_rank last order by upd ) from src
2 group by acct
3 /
ACCT MAX(UPD) MAX(Z)KEEP(DENSE_RANKLASTORDERBYUPD)
---------- --------- ------------------------------------
1 02-JAN-16 2
10 01-JAN-16 11
11 02-JAN-16 12
12 02-JAN-16 12
4 rows selected.
We can then use this to do our merge
SQL>
SQL> merge into t
2 using
3 ( select acct, max(upd) upd, max(z) keep ( dense_rank last order by upd ) z from src
4 group by acct
5 ) s
6 on ( t.tacct = s.acct )
7 when matched
8 then update set tz = z, tupd = upd
9 /
3 rows merged.
SQL>
SQL> select * from t;
TACCT TUPD TZ
---------- --------- ----------
1 02-JAN-16 2
10 01-JAN-16 11
11 02-JAN-16 12
3 rows selected.
SQL>
SQL>