All Forums Database
CrayRay 4 posts Joined 04/16
15 Aug 2016
Performance Issue - Left Join vs Update

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):
Col1  Col2  Col3
a       1      1
a       2      2
a       3      3
b       1      1
b       2      3
b       3      2
Join condition:
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?

You must sign in to leave a comment.