CODEWITHSUNDEEP
sqloracle-database
jijo
jijo

Reputation: 815

Update column based on data in same column

I have a table OFFICE_HOURS which has data for working hours of a physician in an office(site). While loading this table from source system there were some issues and data were loaded incorrectly.
For a phy_id, site_id combination, the hours should be same (duplicates don't matter).
So I need to update the table, so the hours in the first row of a phy_id,site_id combination should be used to update the rest of the rows which have same phy_id,site_id combination.
Actual and expected results below. Please help.

enter image description here

Upvotes: 0

Views: 4998

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

If you dont mind any hour

UPDATE OFFICE_HOURS o 
SET HOURS = (SELECT min(HOURS)
             FROM OFFICE_HOURS f 
             WHERE f.phy_id = o.phy_id
               and f.site_id = o.site_id
            )

If you want something more complex and with options to handle the hour selected.

with office as (
    SELECT *, 
           row_number() over (partition by phy_id, site_id order by hours) as rn
    FROM OFFICE_HOURS
)
UPDATE office o 
SET hours = (SELECT HOURS 
             FROM office f 
             WHERE f.phy_id = o.phy_id
               and f.site_id = o.site_id
               and f.row_id = 1

Upvotes: 2

Related Questions