CODEWITHSUNDEEP
sql
Talib
Talib

Reputation: 13

How to Update the column from another table having minimum value?

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.

Upvotes: 1

Views: 61

Answers (2)

Dhaval Soni
Dhaval Soni

Reputation: 416

Here is sql query to get your answer:

update [dbo].[Tax_Details]
    set Frequency = Frequency_Sequence.Frequency  
  from 
  [Tax_Details] left outer join 
      (select  Tax_Code,min(fs.[Sequence]) as sequence1 
        from [dbo].[Freq_Details] fd
         left outer join [dbo].[Frequency_Sequence] fs on  fd.[Frequency] = fs.[Frequency]
        group by Tax_Code) f1 on [Tax_Details].Tax_Code = f1.Tax_Code
 left outer join Frequency_Sequence on f1.sequence1 =  Frequency_Sequence.Sequence

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 0

Related Questions