CODEWITHSUNDEEP
sqloracle-database
LND
LND

Reputation: 1

Create procedure and update from two columns

I have two tables A and B, i wont to make update to D from A if C from A = C from B and if LB minus LA >=2 hours set D=0

enter image description here

Upvotes: 0

Views: 41

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

MERGE might be one of your choices:

SQL> create table a (c number, d number, la date);

Table created.

SQL> create table b (c number, lb date);

Table created.

SQL> insert into a
  2    (select 12345, 30, to_date('01.04.2018 20:10', 'dd.mm.yyyy hh24:mi') from dual);

1 row created.

SQL> insert into b
  2    (select 12345, to_date('01.04.2018 18:00', 'dd.mm.yyyy hh24:mi') from dual);

1 row created.

SQL>
SQL> merge into a
  2    using (select b.c, b.lb from b) x
  3    on (a.c = x.c and
  4        (a.la - x.lb) * 24 >= 2
  5       )
  6  when matched then
  7    update set a.d = 0;

1 row merged.

SQL>
SQL> select * From a;

         C          D LA
---------- ---------- ----------
     12345          0 01.04.2018

Values that prevent UPDATE:

SQL> delete from a;

1 row deleted.

SQL> insert into a
  2    (select 12345, 30, to_date('01.04.2018 19:10', 'dd.mm.yyyy hh24:mi') from dual);

1 row created.

SQL> merge into a
  2    using (select b.c, b.lb from b) x
  3    on (a.c = x.c and
  4        (a.la - x.lb) * 24 >= 2
  5       )
  6  when matched then
  7    update set a.d = 0;

0 rows merged.

SQL> select * From a;

         C          D LA
---------- ---------- ----------
     12345         30 01.04.2018

SQL>

Upvotes: 1

Related Questions