Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using CASE Statements Expand / Collapse
Author
Message
Posted Thursday, April 24, 2014 6:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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?
Post #1564620
Posted Thursday, April 24, 2014 6:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
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.
Post #1564625
Posted Thursday, April 24, 2014 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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.
Post #1564626
Posted Thursday, April 24, 2014 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1564630
Posted Thursday, April 24, 2014 7:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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.
Post #1564640
Posted Thursday, April 24, 2014 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1564642
Posted Thursday, April 24, 2014 7:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
I am unfamiliar with using join. Is it not possible to modify the code above to just copy all rows?
Post #1564656
Posted Thursday, April 24, 2014 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 13,085, Visits: 12,550
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1564659
Posted Thursday, April 24, 2014 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 28, 2014 6:46 AM
Points: 19, Visits: 37
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.
Post #1564672
Posted Thursday, April 24, 2014 9:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 13,085, Visits: 12,550
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1564686
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse