SQL query update conditional

Question

I have 2 tables

aaa
on_date datetime null,
channel_id numeric(10,0) null,
type_id  numeric(10,0) null,
amount numeric(10,0) null

bbb
channel_id  numeric(10,0) null
type_id numeric(10,0) null

I want to do an update to aaa.amount to 0 of

only the common channel_ids and type_ids when bbb.type_id is not null

or

of only the common channel_ids and ALL the type_ids when the bbb.type_id is null

aaa

2014-09-13 1 3 12

2014-09-13 1 4 16

2014-09-13 2 1 11

bbb (case 1. )

1 3

bbb (case 2. )

1 null

In first case only the first aaa record should have amount = 0 In the second case the first 2 records of aaa should have amount = 0 Thank you

Please provide sample data and desired results.

Gordon Linoff
– Gordon Linoff

2016-03-28 12:40:53 +00:00
Commented Mar 28, 2016 at 12:40 — Gordon Linoff
– Gordon Linoff, Commented Mar 28, 2016 at 12:40

Jibin Balachandran · Accepted Answer · 2016-03-28 12:53:35Z

2

Try this:

update a
set a.amount=0
from aaa a inner join bbb b 
on a.channel_id=b.channel_id
where ((a.channel_id=b.channel_id and a.type_id=b.type_id and b.type_id IS NOT NULL)
OR (a.channel_id=b.channel_id and b.type_id IS NULL))

answered Mar 28, 2016 at 12:53

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi Kavaiya · Accepted Answer · 2016-03-28 12:52:14Z

1

you need to somthing like following as per i understand your question

UPDATE
    aa
SET
    aa.amount  = 0 
FROM
    aaa aa
    JOIN
    bbb bb ON aa.channel_id  =bb.channel_id      
WHERE
    (aa.type_id = bb.type_id AND bb.type_id is not null ) 
    OR
    (bb.type_id is null)

Hope it will help to you.

answered Mar 28, 2016 at 12:52

Ravi Kavaiya

8576 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL query update conditional

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related