Updating columns in Oracle based on values in other tables

Question

I am fairly new to creating and altering tables in SQL (Oracle) and have a question involving updating one table based on values in others.

Say I have table A:

 ID        Date         Status    
---        ---            ---
 1       1/1/2000       Active
 2       5/10/2007      Inactive
 2       2/15/2016      Active
 3       10/1/2013      Inactive
 4       1/11/2004      Inactive
 5       4/5/2012       Inactive
 5       6/12/2014      Active

and table B:

 ID        Date         Status     Number of Records in A
---        ---           ---           ---   
 1       
 2       
 3      
 4      
 5

What is the best way to update table B to get the most recent Date and Status of each item and count of records in A? I know I could join tables but I would like B to exist as its own table.

This has already been answered here: http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table — GavinCattell, Aug 13 '16 at 18:54
@GavinCattell - No, it hasn't. The question you linked to has nothing about either getting the most recent date and status for each id, not about the count of records by id. — , Aug 13 '16 at 18:57
@mathguy True, when I first read it I didn't consider that part. That makes it more complicated, however the linked question hopefully helps along the right track. — GavinCattell, Aug 13 '16 at 19:12

score 0 · Answer 1 · 2016-08-13T19:24:20.337

Something like this. If you already have a table B and you need to populate it with the values from this query, or if you need to create a new table B with these values, adapt as needed. NOTE: I used dt as a column name, since "date" is a reserved word in Oracle. (For the same reason I used "ct" for "count".)

with
     table_A (  id, dt, status ) as (
        select 1, to_date( '1/1/2000', 'mm/dd/yyyy'), 'Active'   from dual union all
        select 2, to_date('5/10/2007', 'mm/dd/yyyy'), 'Inactive' from dual union all
        select 2, to_date('2/15/2016', 'mm/dd/yyyy'), 'Active'   from dual union all
        select 3, to_date('10/1/2013', 'mm/dd/yyyy'), 'Inactive' from dual union all
        select 4, to_date('1/11/2004', 'mm/dd/yyyy'), 'Inactive' from dual union all
        select 5, to_date(' 4/5/2012', 'mm/dd/yyyy'), 'Inactive' from dual union all
        select 5, to_date('6/12/2014', 'mm/dd/yyyy'), 'Active'   from dual
     ),
     prep ( id, dt, status, rn, ct ) as (
       select id, dt, status,
              row_number() over (partition by id order by dt desc),
              count(*) over      (partition by id)
       from   table_A
     )
select id, to_char(dt, 'mm/dd/yyyy') as dt, status, ct
from   prep
where  rn = 1
;


        ID DT         STATUS           CT
---------- ---------- -------- ----------
         1 01/01/2000 Active            1
         2 02/15/2016 Active            2
         3 10/01/2013 Inactive          1
         4 01/11/2004 Inactive          1
         5 06/12/2014 Active            2

Added: You mentioned you are pretty new at this... so: for example, if you need to create table_B with these results, and table_A already exists and is populated: FIRST, you will not need the "table_A" factored subquery in my solution; and SECOND, you will create table_B with something like

create table table_B as
with
     prep ( .....)   --  rest of the solution here, up to and including the ;

NOTE: Both my solution and Gordon's suffer from the same problem, but in a different way. What is the requirement if you have TWO or more rows for the same id and date? My solution will pick one STATUS at random. Gordon's solution will return whatever the MAX(STATUS) is in alphabetical order for that id. Both "solutions" in this case will likely fail your business requirement. — , Aug 13 '16 at 19:51

score 0 · Accepted Answer · answered Aug 13 '16 at 19:19

Oracle lets you assign multiple columns at once in an update statement. So, you can do:

update b
    set (dt, status, ct) =
         (select max(dt),
                 max(status) keep (dense_rank first order by dt desc),
                 count(*)
          from a
          where a.id = b.id
         ) ;

You can basically use the subquery -- with a group by -- if you want the results for all ids as a query:

select max(dt),
       max(status) keep (dense_rank first order by dt desc),
       count(*)
from a
group by id;

You can also use create table as or insert into to put the records directly into b, without having to match them up using update.

Updating columns in Oracle based on values in other tables

2 Answers2