CODEWITHSUNDEEP
sqlt-sql
Looking_for_answers
Looking_for_answers

Reputation: 343

update multiple columns with case/if statement?

Thank you in advance:

I have a table1:

id       ||   batches  || IC_chips

DRG001   ||   JHL001   || layer1
DRG001   ||   JHL001   || layer2
DRG001   ||   JHL001   || layer3
DRG001   ||   JHL001   || layer4
DRG001   ||   JHL001   || layer5
DRG001   ||   JHL001   || layer6
DRG001   ||   JHL002   || layer7
DRG001   ||   JHL002   || layer8
DRG001   ||   JHL002   || layer9
DRG001   ||   JHL002   || layer10
POQ001   ||   ADG001   || layer1
POQ001   ||   ADG001   || layer2
POQ001   ||   ADG001   || layer3
POQ001   ||   ADG001   || layer4
POQ001   ||   ADG001   || layer5
POQ001   ||   ADG001   || layer6

the output table is :

ID     ||   print_batch_1   ||  Print_batch_2  ||  Count_print_batch_1  || Count_print_batch_2  ||  Batch_count
DRG001 ||   JHL001          ||  JHL002         ||  06                   || 04                    ||  02
POQ001 ||   ADG001          ||  Null           ||  06                   || Null                  ||  01

i tried it with an update statement but i am facing an issue when their are more than one Print batches.

this is the code i tried with :

update tab
set Count_name=b.Count_name
,batch_count=b.batch_count
from
table1 tab
inner join
(
select Name, count(batches) as Count_name, count(distinct batches) as 
batch_count
from table1
group by batches) b
on tab.batches=b.batches

Upvotes: 0

Views: 70

Answers (1)

Amey
Amey

Reputation: 130

Please try this -

update tab B
set Count_name = a.batches, batch_count = batch_cnt
from    (
            select  batches, count(*) batch_cnt
            from    table1 A
            where   a.batches = b.batches
        )
where
    exists (
                select  1
                from    table1 A
                where   a.batches = b.batches
            )

Upvotes: 1

Related Questions