Using CASE Statements

Question

Post reply

Using CASE Statements

mlwiller2

SSChasing Mays

Points: 647
More actions
April 24, 2014 at 6:22 am

#146870

I'm having some trouble grasping the use of CASE statements (used to using If/Then). I would like to convert this to CASE format:
If DATEPART(Month,Datetime) = 04
Then UPDATE DB1
SET column1 = (SELECT Value FROM DB2)
So if the month of the current datetime matches 4 (April), then update column1 of DB1 with the values in the Value column of DB2. How would this look using CASE?

Viewing 11 posts - 1 through 10 (of 10 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

Where is column Datetime coming from?

You should post your DDL and some sample data as DML along with the results you want to see.

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 2

I think I mean to use CURRENT_TIMESTAMP as I want to compare to today's date/time. I don't really have a sample since this is just theoretical right now.

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

mlwiller2 (4/24/2014)
I'm having some trouble grasping the use of CASE statements (used to using If/Then). I would like to convert this to CASE format:
If DATEPART(Month,Datetime) = 04
Then UPDATE DB1
SET column1 = (SELECT Value FROM DB2)
So if the month of the current datetime matches 4 (April), then update column1 of DB1 with the values in the Value column of DB2. How would this look using CASE?

IF... conditionally runs TSQL statements. If you want to update DB1 from DB2 only on the second Tuesday of the month, then use IF.

CASE conditionally returns value choices within TSQL statements and doesn't sound appropriate for usage here. Traditionally you would provide sample scripts so folks can develop code against it. Let's try a short cut. Start by writing a SELECT query which joins DB1 and DB2 and returns DB1.column1 and DB2.Value, we'll take it from there.

^{“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

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 4

Yeah CASE was the wrong term to use. Looks like I want something like this:

IF ((SELECT DATEPART(MONTH, GetDate())) = 04)

UPDATE

db1

SET

column1 = db2.value

FROM

db2

However, when using this it copies only the first row from db2 into all rows of db1. I want all rows of db2 into all rows of db1 for column1.

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

mlwiller2 (4/24/2014)
Yeah CASE was the wrong term to use. Looks like I want something like this:
IF ((SELECT DATEPART(MONTH, GetDate())) = 04)
UPDATE
db1
SET
column1 = db2.value
FROM
db2
However, when using this it copies only the first row from db2 into all rows of db1. I want all rows of db2 into all rows of db1 for column1.

Start by writing a SELECT query which joins DB1 and DB2 and returns DB1.column1 and DB2.Value, we'll take it from there.

^{“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

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 6

I am unfamiliar with using join. Is it not possible to modify the code above to just copy all rows?

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

mlwiller2 (4/24/2014)
I am unfamiliar with using join. Is it not possible to modify the code above to just copy all rows?

Yes it is possible. The problem is that the code you posted does not indicate which row in db2 is related to a row in db1. This is where the join comes into play. It is the logic to know which row is related to which row(s) in another table.

You should read up on them. Here is a place to start. http://technet.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx

This is fundamental to relational data. If you don't understand this concept you are really going to struggle with things like the update you trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 8

It looks like I need to have some kind of ID column on each table. I might have to adjust some of the other code since right now the tables get completely truncated every time the query is ran, which would remove the ID values.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

mlwiller2 (4/24/2014)
It looks like I need to have some kind of ID column on each table. I might have to adjust some of the other code since right now the tables get completely truncated every time the query is ran, which would remove the ID values.

You don't necessarily need to have an ID column but you should have a primary key on both tables. It sounds like whatever you are doing is part of a larger process. It is nearly impossible to help much because we don't really have any details to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MMartin1 One Orange Chip Points: 27799 More actions · Answer 10

A typical update using a IF could look like

IF (month(getdate())=4)

begin

update t1

set t1.columnA=t2.columnA

from table1 as t1 inner join table2 as t2

ON t1.someOtherColumn=t2.someOtherColumn

END

ELSE

begin

--- do something else

end

See if this helps you get started

----------------------------------------------------