Updating a db table based on 2 separate conditions

Question

I need to update my table (dependency) and set a new column (vid) to the nextval of a sequence. I can't change the way data is in the table so please don't mention removing redundant data.

However, the where condition is the issue as 2 conditions need to be met (the cols provid and origid). There is a separate primary key (prikey). The table looks like this:-

Table dependency (
  varchar prikey(46),
  varchar provid(46),
  varchar origid(46)
  varchar otherdata(16)
  vid integer
)

Sample Data would look like this:-

(prikey, ORIGID, PROVID, VID) 
('AA1', 'C3C87F000001', '23', 1), 
('AA2', 'C3C87F000001', '23', 1), 
('AA3', 'C3C87F000001', '26', 2), 
('AA4', 'C3C87F000001', '53', 3)
('AA5', 'A3C87F000009', '26', 4), 
('AA6', 'A3C87F000009', '23', 5)

As can be seen from the data, the prikey is unique, the origid and provid (which are actually Foreign Keys) may repeat. For the sake of this example, I have added in the vid values as they should be. A vid basically identifies a row which has the same origid and provid.

My Question:- what is the sql like for updating this table setting vid = sequence.nextval where origid and distinct(provid)?

Answer 1

Try:

merge into dependency
USING (SELECT DENSE_RANK() OVER (ORDER BY prikey, ORIGID, PROVID) g, prikey rid FROM dependency) SOURCE
ON (dependency.prikey = SOURCE.prikey)
WHEN MATCHED THEN UPDATE SET dependency.vid = SOURCE.g