update data based on case condtion

Question

update data based on case condtion

smer

SSCommitted

Points: 1684
More actions
January 28, 2015 at 5:31 am

#294277

Hi Experts !
I have two sql queries needs to run on condition. In Sub Query if the column having value 0 then run first update statement else second statement?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

farrukhhameed786 (1/28/2015)
Hi Experts !
I have two sql queries needs to run on condition. In Sub Query if the column having value 0 then run first update statement else second statement?

Yes, you can probably do this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

smer SSCommitted Points: 1684 More actions · Answer 2

smer

SSCommitted

Points: 1684

January 28, 2015 at 6:40 am

#1773665

Hi

How to do this?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

farrukhhameed786 (1/28/2015)
Hi
How to do this?

Without seeing the code you have already written which would help to describe your problem, it would have to be a guess. Something like

UPDATE MyTable SET

column1 = CASE WHEN AnotherColumn = 1 THEN 'Yes' ELSE 'No' END,

column2 = CASE WHEN AnotherColumn = 2 THEN 23 ELSE NULL END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

smer SSCommitted Points: 1684 More actions · Answer 4

Sorry edited ...

I need if VAL42 ( value ) greater then 0 then run first update query and if VAL42 (value) is equal to 0 then run second update statement.

IF NOT EXISTS (SELECT
(EVT.VAL42 )
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING='TABLET' AND EVT.VAL42 =0)
UPDATE TABLE1 SET VAL74=
(SELECT
(EVT.VAL91)
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
AND ip.PROPERTY_STRING='TOOL'
AND TABLE1.START_DATETIME=EVT.START_DATETIME)
/
( (SELECT
(EVT.VAL42 )
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING='TABLET'
AND TABLE1.START_DATETIME=EVT.START_DATETIME
) )
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_PROPERTY WHERE PROPERTY_STRING
IN ('TEL1') )
AND TABLE1.START_DATETIME>='1/23/2015'
AND TABLE1.START_DATETIME<='1/26/2015'
ELSE
UPDATE TABLE1 SET VAL74=
((SELECT
(EVT.VAL25)
FROM TABLE1 EVT INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID
INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING IN
('Storage')
AND TABLE1.START_DATETIME=EVT.START_DATETIME ) )
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_PROPERTY WHERE PROPERTY_STRING
IN ('TEL1') )
AND TABLE1.START_DATETIME>='1/23/2015'
AND TABLE1.START_DATETIME<='1/26/2015'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

farrukhhameed786 (1/29/2015)
I am doing like that but if EVT.VAL42 >0 the first update query not run it goes to second update
IF NOT EXISTS (SELECT
(EVT.VAL42 )
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING='TABLET' AND EVT.VAL42 >0)
UPDATE TABLE1 SET VAL74=
(SELECT
(EVT.VAL91)
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
AND ip.PROPERTY_STRING='TOOL'
AND TABLE1.START_DATETIME=EVT.START_DATETIME)
/
( (SELECT
(EVT.VAL42 )
FROM TABLE1 EVT
INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING='TABLET'
AND TABLE1.START_DATETIME=EVT.START_DATETIME
) )
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_PROPERTY WHERE PROPERTY_STRING
IN ('TEL1') )
AND TABLE1.START_DATETIME>='1/23/2015'
AND TABLE1.START_DATETIME<='1/26/2015'
ELSE
UPDATE TABLE1 SET VAL74=
((SELECT
(EVT.VAL25)
FROM TABLE1 EVT INNER JOIN ITEM ID ON ID.ITEM_ID=EVT.ITEM_ID
INNER JOIN ITEM_PROPERTY ip ON ID.ITEM_ID=ip.ITEM_ID
WHERE ip.PROPERTY_STRING IN
('Storage')
AND TABLE1.START_DATETIME=EVT.START_DATETIME ) )
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_PROPERTY WHERE PROPERTY_STRING
IN ('TEL1') )
AND TABLE1.START_DATETIME>='1/23/2015'
AND TABLE1.START_DATETIME<='1/26/2015'

I can't really tell from this what you are trying to do. Can you explain it in words?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

smer SSCommitted Points: 1684 More actions · Answer 6

smer

SSCommitted

Points: 1684

January 29, 2015 at 6:39 am

#1773967

I edited the post sorry my English not good.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Run this query and post back the results:

SELECT

[EVT.VAL42 = 0] = SUM(CASE WHEN EVT.VAL42 = 0 THEN 1 ELSE 0 END),

[EVT.VAL42 <> 0] = SUM(CASE WHEN EVT.VAL42 <> 0 THEN 1 ELSE 0 END)

FROM TABLE1 EVT

INNER JOIN ITEM ID

ON ID.ITEM_ID = EVT.ITEM_ID

INNER JOIN ITEM_PROPERTY ip

ON ID.ITEM_ID = ip.ITEM_ID

WHERE ip.PROPERTY_STRING = 'TABLET'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

smer SSCommitted Points: 1684 More actions · Answer 8

smer

SSCommitted

Points: 1684

February 4, 2015 at 2:39 pm

#1775477

Thank you...

It works for me.

Great