Update a specific column with data from table1 column to table2 column only when where clause matched using oracle SQL

0
votes

I am looking for assistance for Oracle SQL query for updating table1 column3 with table2 column3 based on where clause where table3 column1 is equal to table2 column1 and table2 column2 is equal to table1 column2. Then update table1 column3 with table2 column2. I have around 500 records i need update only for specific where clause conditions.

I tried below query but no luck. Any assistance is appreciated:

UPDATE t1 set t1.column3= t2.column3
FROM table1 t1
INNER JOIN table1 t1, table2 t2, table3 t3 on t3.column1 = t2.column1
AND t1.column2 = t2.column2
and t1.date between to_date('20190415 00:00:00', 'yyyymmdd hh24:mi:ss') 
AND to_date('20190415 15:59:59', 'yyyymmdd hh24:mi:ss');

I expect query to execute between certain dates only.

2
sqloraclejoinsql-update
stackoverflow.com/search?q=[oracle]+update+join – a_horse_with_no_name

2 Answers

0
votes

You seem to want something like this:

update table1 t1
    set column3 = (select t2.column3
                   from table2 t2 join
                        table3 t3
                        on t3.column1 = t2.column1
                   where t1.column2 = t2.column2 and
                         t1.date >= date '2019-04-05' and
                         t1.date < date '2019-04-16'
                  );

EDIT:

If not all rows match the conditions, you need to repeat the query in the where:

update table1 t1
    set column3 = (select t2.column3
                   from table2 t2 join
                        table3 t3
                        on t3.column1 = t2.column1
                   where t1.column2 = t2.column2 and
                         t1.date >= date '2019-04-05' and
                         t1.date < date '2019-04-16'
                  )
where exists (select t2.column3
              from table2 t2 join
                   table3 t3
                   on t3.column1 = t2.column1
              where t1.column2 = t2.column2 and
                    t1.date >= date '2019-04-05' and
                    t1.date < date '2019-04-16'
             );
0
votes

Is your column3 in table t1 defined with not null constraint. You can try with a not null clause in the inner queries or can use a NVL function to replace the NULL values with a suitable value.

update table1 t1
    set column3 = (select t2.column3
                   from table2 t2 join
                        table3 t3
                        on t3.column1 = t2.column1
                   where t1.column2 = t2.column2 and
                         t1.date >= date '2019-04-05' and
                         t1.date < date '2019-04-16' and 
                         t2.column3 is not null
                  )
where exists (select t2.column3
              from table2 t2 join
                   table3 t3
                   on t3.column1 = t2.column1
              where t1.column2 = t2.column2 and
                    t1.date >= date '2019-04-05' and
                    t1.date < date '2019-04-16' and
                    t2.column3 is not null
             );