SQL Query to find distinct records and Update a table

0
votes

I have a table ABC which has following records -

  INVC_NUM    | LINE_NUMBER  |  ORGANIZATION_ID  | STATUS    |  DATE

  0000034454       1             521                 Validated   09/09/2016 

  0000034454       2             521                 Validated    04/09/2016 

  0000034410       1             521                 Validated    04/09/2016

  0000034410       2             521                 notValidated    04/09/2016

  0000034410       3             521                 notValidated   04/09/2016

  0000034410       4             521                 Validated   04/09/2016

Now I need to update a table XYZ which has following columns 

INVC_NUM     |   ORGANIZATION_ID   | date

0000034454   |   521               | 09/09/2016    
0000034410   |   521               | null

Whenever the status for all the Line_number's for particular INVC_NUM and organization_id are "ALL" validated(status)then we need to update XYZ with sysdate.

Whenever the status for all the Line_number's for particular INVC_NUM and organization_id are NOT ALL of them validated(Status) then we need to update XYZ with NULL. XYZ has unique record for each INVC_NUM and Org_id.

2
sqloracle

2 Answers

1
votes

In order to know whether all statuses for an INVC_NUM and organization_id are valid, see if you find an invalid one:

update xyz
set date =
case when not exists
  (
    select *
    from abc
    where abc.invc_num = xyz.invc_num
      and abc.organization_id = xyz.organization_id
      and abc.status = 'notValidated'
  ) then sysdate 
end;

(Omitting the ELSE branch defaults to null, which is what we want. If you consider it more readable, change then sysdate end to then sysdate else null end.)

1
votes

You can do this with a correlated subquery. It uses aggregation to return one row:

update xyz
    set date = (select (case when count(*) = 0 then sysdate end)
                from abc
                where abc.status = 'NotValidated' and
                      abc.invc_num = xyz.invc_num and
                      abc.organization_id = xyz.organization_id
               );