UPDATE by matching ROW_NUMBER with another table?

Question

This can be done fine in SQL Server (whereas we can use FROM clause in UPDATE statement). But I'm fairly new to Oracle SQL. The scenario is simple like this. I have 2 tables (having the same number of rows). Now I need to update one column of this table to one column of another table by matching row_number of each table.

I've referenced the Oracle UPDATE and this looks like the most promising approach:

update 
(select * from 
 (select A1, 
         row_number() over (order by A1) as rn from A) d 
 join 
 (select B1, 
         row_number() over (order by B1) as rn from B) v on d.rn = v.rn)
set A1 = B1;

but it threw error saying this:

cannot modify a column which maps to a non key-preserved table

There are some examples involving UPDATE like this but it's not exactly like in my scenario because the matching condition does not involve any existing column of the base table (the table we need to update)

@KaushikNayak no, they are just normal columns of the same data type. — Hopeless, Jul 26 '18 at 08:09

Kaushik Nayak · Accepted Answer · 2018-07-26T15:20:49.003

A table should ideally have a Primary key or a Unique key in order to identify the rows. And if two tables are considered to be related, either there should be a foreign key linking each other or a common set of columns which uniquely identify the records. If not, it violates the basic RDBMS principles. row_number() is a function which generates numbers and not a physical key that exists in the table. You should consider changing your design to make use of efficient data extraction methods and simpler queries.

As a workaround, you may use a MERGE statement usingROWID. However, do note that there could be chances where this might not work as expected if the number of rows mismatch or some other scenario I can't think of.

MERGE INTO a 
using (SELECT a.a1, 
              b.b1, 
              a.rid 
       FROM   (SELECT a.*, 
                      row_number() 
                        OVER ( 
                          ORDER BY a1 ) AS rn, 
                      a.rowid           AS rid 
               FROM   a) a 
              JOIN (SELECT b.*, 
                           row_number() 
                             OVER ( 
                               ORDER BY b1 ) AS rn 
                    FROM   b) b 
                ON ( a.rn = b.rn )) s 
ON ( a.rowid = s.rid ) 
WHEN matched THEN 
  UPDATE SET a.a1 = s.b1;

Demo

The reason this is not a straightforward update is because the rows in a table do not have a default ordering. Hence it is essential to have a key like primary key to give us a desired order.

yes, thank you this works expectedly. The point here is using `rowid` which is some built-in hidden column existed on any table (not like `row_number()`). Without such kind of column we cannot update the table column in this scenario (I've found some solutions with MERGE before but they are different from this). Also the design here is not for production, I just wanted to update data for testing purpose (copying data for testing) , of course we always need standard foreign keys to link tables. — Hopeless, Jul 30 '18 at 02:15

Amith Kumar · Answer 2 · 2018-07-26T10:07:46.523

1

You can achieve your desired result by sequence of statements, but there is no direct one statement solution to this:

SOLUTION 1: Alter Table

--alter table to add a column for sequencing
alter table A
add(rownumber number);
alter table B
add(rownumber number);

--add sequencing
update A
set rownumber = rownum;
update B
set rownumber = rownum;

--update Data
update A
set A1 = (select B1 from B where A.rownumber = B.rownumber)

--undo DDL changes
alter table A
drop column rownumber;
alter table B
drop column rownumber;

SOLUTION 2: Use Third Table

CREATE global TEMPORARY TABLE ab 
  ( 
     a1 VARCHAR(10), 
     b1 VARCHAR(10) 
  ) 
ON COMMIT DELETE ROWS; 

INSERT INTO ab 
(SELECT a1, 
        b1 
 FROM   (SELECT a1, 
                Row_number() 
                  over ( 
                    ORDER BY a1) AS rna 
         FROM   a) xa 
        join (SELECT b1, 
                     Row_number() 
                       over ( 
                         ORDER BY b1) AS rnb 
              FROM   b) xb 
          ON xa.rna = xb.rnb); 

UPDATE a 
SET    a1 = (SELECT b1 
             FROM   ab 
             WHERE  ab.a1 = a.a1); 

DROP TABLE ab;

Using Oracle Merge would had been a possible solution, if your row_number column was different than column to update, as merge doesn't support updating the same column used for ON clause.

edited Jul 26 '18 at 10:07

answered Jul 26 '18 at 08:28

Amith Kumar

4,400
1
21
28

actually I'm searching for statements (can be multiple) but should not be altering-table statements. Because in some cases that's impossible and moreover I need an equivalent solution from SQL Server. – Hopeless Jul 26 '18 at 08:39
Unfortunately, AFAIK Oracle doesn't support this, since your row sequencing column A1/B1 is the one you are trying to update. If your `row_number` could be based on any other column but one you are updating, then it was doable. – Amith Kumar Jul 26 '18 at 09:43
Added a alternate solution using Global temporary table. – Amith Kumar Jul 26 '18 at 10:08
in SQL Server we don't need to alter/create anything, just query normally. So at first I've just been looking for such a solution. However looks like this is a ridiculous limitation of Oracle UPDATE design and we cannot enjoy the easiness here. I will consider accepting your answer soon if not any other adding a better solution. – Hopeless Jul 26 '18 at 11:54
because the solution provided by Kaushik Nayak works and looks better, so I would accept his answer instead, but this answer of yours deserves one upvote from me. Thanks anyway. – Hopeless Jul 30 '18 at 02:16
Yeah I agree, using rowID makes it more relevant. – Amith Kumar Jul 30 '18 at 06:11

UPDATE by matching ROW_NUMBER with another table?

2 Answers2