0

I have 2 tables (with 92 columns) with same columns but with different data. PK is Report_NR and File_Number

Table1: Original_Report:

Report_Nr    File_Number    Time   Minute   Work_Hours  .........
--------------------------------------------------
1              1            3       2        6
2              1            5       1        8
3              1            2       4        7

Table2: Modified_Report:

Report_Nr    File_Number    Time   Minute   Work_Hours  .........
--------------------------------------------------
1              1            3       2        8
2              1            5       1        8
3              1            1       2        7
4              1            5       6        6

Now I want to search for new or modified data entered from Table 2 and then to update Table 1 to be same as Table 2. For example, in my case, for report_id 1 update only column with modified data: Work_Hours. So I need to update only this column,for report_id 2, data is same in both tables so no update needed, for 3rd row need to update only time and minute and for 4th row I will need to insert all columns.

With this select I have identified new rows and modified rows:

-- NEW ROWS
SELECT A.*, 'NEW'
FROM Modified_Report  A
LEFT JOIN Original_Report B ON B.ID = A.ID

UNION
-- MODIFIED ROWS
SELECT B.*, 'MODIFIED' 
FROM (
        SELECT * FROM Modified_Report 
        MINUS
        SELECT * FROM Original_Report 
    ) S1
INNER JOIN Original_Report B ON S1.ID = B.ID;

Here I found sme info with merge for update: update one table with data from another

but I need this with unknown number of columns to be updated: for a row I can have 2 columns that I need modified or 10 or 40. Also I will need what column(s) is/are modified, and how many changes found for each column.

Will be a solution to use a procedure or function or if you have another idea?

I used a Oracle DB with SQL Developer

Community
  • 1
  • 1
Silviu S
  • 11
  • 1
  • Why not just `truncate` the second table and copy the data into it? Also, you might be interested in `merge`. – Gordon Linoff Mar 13 '17 at 13:00
  • My client don't desire to truncate original table and I must find another method. Also I try to use merge but I found example with a known number of colums and I will need for unknown number of columns. – Silviu S Mar 13 '17 at 13:07
  • Why not update table 1 with the contents of table 2? In your example (report_nr=1)you will update time and minute with the exact same value. No harm done. – Rene Mar 13 '17 at 13:11
  • I forgot to say that I have more data in Table 1 that in Table 2 (for this I don't need to update rows). So I will need to update Table 1 only with values(columns) that are modified or added in Table 2. So in the end, in table 1, I must have all data from Table 2 and also data that already exist and it is not in Table 2 – Silviu S Mar 13 '17 at 13:27
  • Something is unclear in this requirement. You have 92 columns, let's say, and for one row there are only two columns where you must change the value from Table 1 to match the data from Table 2; the other 90 columns should not be modified, because the values in the two tables are equal. **How do you know that?** You need to do a comparison for each of those other columns, right? Do you expect that a comparison takes less time or resources than an assignment? (Otherwise why don't you want ALL columns to be updated?) **continued below** –  Mar 13 '17 at 13:44
  • Do you expect 2 assignments and 90 comparisons to take less time than 92 assignments, 0 comparisons? Or do you think every individual field is updated on disk? That is not how it works; an entire row is overwritten from memory to disk, even if only two columns out of 92 have changed values. –  Mar 13 '17 at 13:46
  • Mathguy, I understand your point that an entire row is overwritten from memory to disk, even if only two columns out of 92 have changed values. My problem is that client desire something more complex. They have a table with all columns from this report as rows and a column that say if they want to modify column or not. In my example, if column Time has value 'N' (they don't want to modify) and is different on Table 2 than Table 1 they don't want to update in Table 1(rest of column if are with 'Y' I need to update). This is why I try to find a solution to modify only some columns and not all. – Silviu S Mar 13 '17 at 14:43
  • I see. That (by "that" I mean the additional inputs, the client providing 'Y' and 'N' values) should be part of your problem statement. Now: that can still be done with an UPDATE or a MERGE statement, that will take these additional inputs into consideration... except that you probably need **dynamic SQL** if you must read which columns are allowed to be modified from that additional table each time you run the query. –  Mar 13 '17 at 21:58

0 Answers0