Consider table Tax_Details with below details. In this table, each and every Tax_code should have unique Frequency
[Tax_Code] [Frequency]
A-001
A-002
A-003
B-001
C-001
Consider another table Freq-Details with Below details. In this table, a Tax_code may have multiple frequency
[Tax_Code] [Frequency]
A-001 Weekly
A-001 Bi-Weekly
A-001 Daily
A-002 Daily
A-002 Monthly
A-003 Bi-Weekly
A-003 Monthly
B-001 Daily
B-001 Monthly
C-001 Monthly
Consider 3rd table Frequency_Sequence with details. In this table, each and every frequency have some sequence.
[Frequency] [Sequence]
Daily 1
Weekly 2
Bi-Weekly 3
Monthly 4
I want to update [Frequency] column in Tax_Details table with Frequency from Freq-Details table and the Sequence of frequency should be minimum. My output should be like this
[Tax_Code] [Frequency]
A-001 Daily
A-002 Daily
A-003 Bi-Weekly
B-001 Daily
C-001 Monthly
Please help to get this. Thanks in advance.
Advertisement
Answer
You can return the results you want using conditional aggregation:
select fd.tax_code,
max(case when seqnum = 1 then fd.frequency end) as frequency
from (select fd.tax_code, fd.frequency,
row_number() over (partition by fd.tax_code order by fs.sequence) as seqnum
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
) fd
group by fd.tax_code;
You can express this in an update as:
update tax_details
set frequency = (select fd.frequency
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
where fd.tax_code = tax_details.tax_code
order by fs.sequence
fetch first 1 row only
);
Both of these are standard SQL and will work in most databases. However, the syntax might differ slightly in any given database.