0

I work in SQL Developer by Oracle. I want attach dates from table 2 into empty column Date in Table 1 only into existed rows/ids. I tried to do it by below code but it doesn't work. It seems easy, but I couldnt find solution.

Table 1                Table 2
ID   Date              ID   Date
33   (null)            33   2021-01-02
22   (null)            22   2019-01-02
100  (null)            100  1999-09-09
200  (null)            200  2005-06-07
44   (null)            44   2010-02-02
                       999  2009-08-06

insert into table1 (date)
select
t2.date
from table2 t2 where table1.id in (select t2.id from table2);
Piotr94
  • 7
  • 4
  • Does this answer your question? [Oracle SQL: Update a table with data from another table](https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) – astentx Sep 28 '21 at 19:41

2 Answers2

0

You want an update statement for existing rows. I’ll assume you haven’t really named your column date.

Something like:

Update table1 t1
Set t1.date_col = (select t2.date_col from table2 t2 where t1.id = t2.id)
Andrew Sayer
  • 2,296
  • 1
  • 7
  • 9
  • I have just run this query and it seems very slow but still working. Up to now it has taken over 10 minutes yet. Each table has about 150k rows. – Piotr94 Sep 28 '21 at 19:30
  • Is there an index to support the scalar subquery? Are there any locks on the table you’re updating that you need to wait behind? – Andrew Sayer Sep 28 '21 at 19:39
  • I have index and no locks. It was done after 20minutes. Thanks for help! But Im still curious about any shorter solution of this problem. – Piotr94 Sep 28 '21 at 20:20
0

think you might be looking for the Oracle Merge statement. If the row exists update it, if not insert it.

MERGE INTO Table1 a
USING Table2 b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE set a.Date = b.Date
WHEN NOT MATCHED THEN
INSERT (id, date)
VALUES (b.id, b.Date);
Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79
  • Thanks for respoonse, I will check your solution tomorrow. So far I have used the code from Andrew. – Piotr94 Sep 28 '21 at 20:22