JasperDangs7 JasperDangs7 - 2 months ago 6
SQL Question

Updating columns in Oracle based on values in other tables

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.

Answer

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.