0

Basically looking at a way to merge a table that has been filled with erroneous data with a table that has good data to "repair" it but the good data I want has quite a bit of repetitious data in it that I need to GROUP so that I can have something to match on.

So in this case simplified down:

Customer_Records - Bad Data in SERIES_ID
| TITLE | SERIES_ID | TYPE |
| EX1   | 00000     |  1   |
| EX2   | 00009     |  1   |
| EX3   | 01234     |  2   |


Series_Records - Good data
| TITLE | SERIES_ID | SUBSERIES_ID |
| EX1   | 98555     | 00001
| EX1   | 98555     | 00002
| EX2   | 98764     | 00098
| EX2   | 98764     | 00108

So in this example I am trying to update the SERIES_ID in the Customer Records table with the SERIES_ID in the Series Record table. I have the Title that i can match on.

What I tried is as follows:

merge into CUSTOMER_RECORDS CR
 using (select TITLE, SERIES_ID from SERIES_RECORDS GROUP BY TITLE, SERIES_ID) SR
    on (CR.TITLE = SR.TITLE)
  when matched then
update set CR.SERIES_ID = SR.SERIES_ID;

When I do this however I get a:

`A stable set of rows could not be got because of large dml activity or a non-deterministic where clause`

Any idea?

LifeTag
  • 23
  • 2
  • Are there rows in the series_record table that have the same title but different series_ids? If so, then you're going to have to work out which row to pick (min/max?), or else another way of tying the series_id to the title. – Boneist Jan 24 '15 at 00:04

1 Answers1

0

If all you care about is series_id, and (title,series_id) in series_records are always the same regardless of subseries_id, then can't you just do this:

merge into CUSTOMER_RECORDS CR
 using (select DISTINCT TITLE, SERIES_ID from SERIES_RECORDS ) SR
    on (CR.TITLE = SR.TITLE)
  when matched then
update set CR.SERIES_ID = SR.SERIES_ID;
OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
  • I tried the above but get the same answer as before: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause. – LifeTag Jan 26 '15 at 15:19
  • Hmm, I ran a setup with your sample data, and the merge worked. So there is something else going on with series_records can cannot be determines from the sample you show vs. the real table data. Does, "select DISTINCT TITLE, SERIES_ID from SERIES_RECORDS" show records with different series_id and the same title? – OldProgrammer Jan 26 '15 at 15:42