Hi everyone, I have 2 tables as below:
Main (20 mil rows, 50 other columns):
Col1 Col2 Col3
a 2 3
b 2 2
Join (50 mil rows, 20 other columns):
a 1 1
a 2 2
a 3 3
b 1 1
b 2 3
b 3 2
Main.Col1 = Join.ColA
Main.Col2 ge Join.ColB
Main.Col3 ge Join.ColC
Qualify 1 = row_number() over (partition by ColA order by ColB desc ColC desc)
In the past (before Col2 and Col3 were introduced) I used:
ALTER TABLE Main to add 20 columns
UPDATE Main, Join etc...
Since we can't do qualify on UPDATE and the join condition above will yield the error "Target row updated by multiple source rows", one way of doing it will be left joining Main and Join (+ qualify).
Are there more efficient ways?