Using CASE Statements

  • 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?

  • 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.

  • 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.

  • 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

  • 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.

  • 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

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

  • 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/

  • 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.

  • 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/

  • 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

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

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

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