Update old data in oracle-CodePudding

hi I have old data and I want to recover them .Lets say I have the following tables .

Table1:

Company	FirstName
All	Beornræd

Table2:

CompanyNames
X
Y
Z

I want to map All to all values of the table of company such that I obtain the following result.

Table1: after update

Company	FirstName
X	Beornræd
Y	Beornræd
Z	Beornræd

I there a way to update them using a query and thanks in advance.

CodePudding user response：

Two simple steps (update (with a cross join) delete) look the simplest to me - at least, with as many info and sample data you provided.

SQL> insert into table1 (company, firstname)
  2    select b.companynames, a.firstname
  3    from table1 a cross join table2 b;

3 rows created.

SQL> delete from table1 where company = 'All';

1 row deleted.

Result:

SQL> select * from table1;

COM FIRSTNAME
--- ---------
X   Beornrad
Y   Beornrad
Z   Beornrad

SQL>

CodePudding user response：

If there are values in table1 Company column other than 'All' then you need to mention t1.Company='All' in where clause.

Select t2.CompanyNames, t1.FirstName
from table1 t1 cross join table2 t2
where t1.Company='All';

CodePudding user response：

One option would be using MERGE as only a single statement such as

MERGE INTO t1
USING (SELECT * FROM t1
       UNION ALL
       SELECT t2.companynames, t1.firstname
         FROM t2, t1) tt
   ON (t1.company = tt.company)
 WHEN NOT MATCHED THEN
                      INSERT VALUES(tt.company, tt.firstname)
 WHEN MATCHED THEN
                  UPDATE SET t1.firstname = ''
                  DELETE WHERE 1 = 1

newly(unmatched rows for t1.company = tt.company) generated rows are inserted, and the matched ones are deleted, where update dml is nothing but an auxiliary representation for deletion

Demo