Use case and join in a stament

  • sebasdoel

    Old Hand

    Points: 322

    Question;

    After trying a lot of different options I wonder if the solution I want to use is possible. The T-SQL below will say enough to show what I mean (I hope so). I used the SQL2000 std db pubs for this test.

    update publishers as p inner join pub_info as i on (p.pub_id=i.pub_id)

    set

    p.state = case

    when p.country LIKE ('USA')

    then '11'

    else p.state

    end,

    p.city = case

    when p.pub_id = '8888'

    then '22'

    end

    where p.country <> 'FLAPDROL' OR

    p.pub_id = '9999'

    As you can see I dont use the join to update table rows. However, the result should be 0 rows affected with no errors. In this version of the t-sql the error will give an error around "as" in line 1.

    Without defining "as" as easy-typo and removing all the p.prefixes other errors will follow. This is just a example of the data I want to update. I need a statement that uses a join for 2 tables where i can define the ifs an whens of 2 different tables.

    Using:

    publishers as p inner join pub_info as i on (p.pub_id=i.pub_id)

    So, does anybody knows how to use all the statements Update, Join, Case, When, Then, Else, end (cast) in a single query (more is good 2:)). If I know the structure of this statement I think I can query the rest. If its not possible to use it like this I really would like to know, saves me a lot of querying.

    thank you a lot for thinking about it.

    Gr,

    Sebastiaan.

    (

    Simpe t-sql I began with:

    update publishers

    set

    state = case

    when country LIKE ('USA')

    then '11'

    else state

    end,

    city = case

    when pub_id = '2222'

    then '33'

    end

    where country <> 'FLAPDROL' OR

    pub_id = '4444'

    )

    works just fine, no errors. but only 1 table, 1 table bad,,, 2 table good.

  • Chad Crawford

    SSChampion

    Points: 11605

    You can do this - the key is in the syntax:

    UPDATE

    SET

    FROM

    WHERE

    So, I think this is what you are looking for:

    update publishers

    set state = case when publishers.country LIKE ('USA') then '11' else publishers.state end,

    city = case when publishers.pub_id = '8888' then '22' end

    from publishers inner join pub_info on (publishers.pub_id=pub_info.pub_id)

    where publisher.country <> 'FLAPDROL' OR

    publishers.pub_id = '9999'

    Note that the "publishers" in the FROM clause is the SAME (as in the exact same, not a copy as it is in a self-join) as the one in the UPDATE clause.

    And I know you gave this just as an example, but when you are setting the city, if the pub_id isn't 8888, then it will set the city to be NULL (there is no "else" clause there).

    Hope this helps!

    Chad

  • sebasdoel

    Old Hand

    Points: 322

    Chad,

    Thank you a lot, this was the correct answer, It was very usefull for updating the addresses table in the Live database.

    Greets

    Sebastiaan.

  • webappguru

    Valued Member

    Points: 68

    Is there any way to put a case statement within a join like this

    SELECT * FROM DESSERTS

    CASE WHEN ____ = ____ then

    JOIN someTable on x.TableName1 = desserts.TableName1

    When ____ = _____ then

    JOIN someTable on x.TableName2 = desserts.TableName2

    Else

    Join someTable on x.TableName3 = desserts.TableName3

    Sorry for the mulitple edits of this. For some reason the space bar was submitting it... Weird!!!

    So basically depending on a particular case I want to join a main table with a secondary table.

    Thanks

  • GSquared

    SSC Guru

    Points: 260824

    You can't do precisely that, but you can have a join with conditional logic, or you could have an inline sub-query in a case statement if you just want one column from the other tables.

    For example:

    select *

    from Table1

    left outer join Table2

    on Table1.Col2 = Table2.Col2

    and Table1.Col1 = 2

    left outer join Table3

    on Table1.Col2 = Table3.Col2

    and Table1.Col1 = 3;

    or

    select *,

    case Col1

    when 2 then

    (select Col3

    from Table2

    where Col2 = Table1.Col2)

    when 3 then

    (select Col3

    from Table3

    where Col2 = Table1.Col2)

    end

    from Table1;

    The first one joins to both tables, but only pulls data from Table2 when Col1 = 2, and only pulls data from Table3 when Col1 = 3.

    The second one pulls one column of data from Table2 or Table3, depending on the value of Col1 in Table1.

    Does that help?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • webappguru

    Valued Member

    Points: 68

    Ok Imagine this scenario... now I am just inheriting this and not the original architect.... And my apologies for being generic but to show real data would make it even more confusing.

    So say you have a table with 500 clients, but some are Diferent Branches

    So say the 1st client is Acme SQL Servers (like that one? :))

    Acme SQL Servers is in Field A

    In Field B there are East, West, Central

    In Field C there are North East, North West, South East, SouthWest, East Central, West Central

    So it looks like this

    Acme SQL Servers East North East

    Acme SQL Servers East South East

    Acme Routers

    Acme Computers East

    Acme Computers West

    Acme Hard Drives East North West

    And you have a sales table that you want to join it with

    Now here is where it gets interesting:

    Inserting a key in the sales data is not an option.

    There are some fields in the sales table that will allow you to extrapulate who the client was but this is what is requiring the cases...

    If a certain code has data in Field A,B & C then we need to join on a certain field

    If there is no data in c but there is in A & B then we would join on a different field

    If there is no data in b & c but there is in A then we would join on a different field

    The only way I could think of doing it (but dont laugh here, it seems to work) is:

    Select all the data where field C is not null

    UNION

    Select all the data where field c is null but field a & b are not null

    UNION

    Select all the data where field B & C Are null but not A

    This is a very taxing pull on the server because we are talking about returning anywhere from 1-8 million records.

  • GSquared

    SSC Guru

    Points: 260824

    I'm having trouble visualizing what you're describing. Can you provide a sample table (create table script) and a sample insert command, to create the data you're describing? Just a few rows, so I can understand a bit better.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • busterDimartino

    Newbie

    Points: 6

    GSquared - Thursday, April 9, 2009 11:44 AM

    You can't do precisely that, but you can have a join with conditional logic, or you could have an inline sub-query in a case statement if you just want one column from the other tables.For example:select *from Table1left outer join Table2 on Table1.Col2 = Table2.Col2 and Table1.Col1 = 2left outer join Table3 on Table1.Col2 = Table3.Col2 and Table1.Col1 = 3;orselect *,case Col1 when 2 then (select Col3 from Table2 where Col2 = Table1.Col2) when 3 then (select Col3 from Table3 where Col2 = Table1.Col2)endfrom Table1;The first one joins to both tables, but only pulls data from Table2 when Col1 = 2, and only pulls data from Table3 when Col1 = 3.The second one pulls one column of data from Table2 or Table3, depending on the value of Col1 in Table1.Does that help?

  • busterDimartino

    Newbie

    Points: 6

    GSquared - Thursday, April 9, 2009 11:44 AM

    You can't do precisely that, but you can have a join with conditional logic, or you could have an inline sub-query in a case statement if you just want one column from the other tables.For example:select *from Table1left outer join Table2 on Table1.Col2 = Table2.Col2 and Table1.Col1 = 2left outer join Table3 on Table1.Col2 = Table3.Col2 and Table1.Col1 = 3;orselect *,case Col1 when 2 then (select Col3 from Table2 where Col2 = Table1.Col2) when 3 then (select Col3 from Table3 where Col2 = Table1.Col2)endfrom Table1;The first one joins to both tables, but only pulls data from Table2 when Col1 = 2, and only pulls data from Table3 when Col1 = 3.The second one pulls one column of data from Table2 or Table3, depending on the value of Col1 in Table1.Does that help?

    I tried the following script in SQL Server 2014, which returned an error:

    Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =,
    !=, <, <= , >, >= or when the subquery is used as an expression.

    SELECT *,
    case city
    WHEN 'London' THEN
    (SELECT shipcity FROM [Sales].[Orders] o WHERE o.custid = c.custid)
      WHEN 'Berlin' THEN
    (SELECT shipcity FROM [Sales].[Orders] o WHERE o.custid = c.custid)
    END
    FROM [TSQLV_3].[Sales].[Customers] c

  • jonathan.crawford

    SSCertifiable

    Points: 6251

    busterDimartino - Wednesday, March 13, 2019 9:22 AM

    GSquared - Thursday, April 9, 2009 11:44 AM

    You can't do precisely that, but you can have a join with conditional logic, or you could have an inline sub-query in a case statement if you just want one column from the other tables.For example:select *from Table1left outer join Table2 on Table1.Col2 = Table2.Col2 and Table1.Col1 = 2left outer join Table3 on Table1.Col2 = Table3.Col2 and Table1.Col1 = 3;orselect *,case Col1 when 2 then (select Col3 from Table2 where Col2 = Table1.Col2) when 3 then (select Col3 from Table3 where Col2 = Table1.Col2)endfrom Table1;The first one joins to both tables, but only pulls data from Table2 when Col1 = 2, and only pulls data from Table3 when Col1 = 3.The second one pulls one column of data from Table2 or Table3, depending on the value of Col1 in Table1.Does that help?

    I tried the following script in SQL Server 2014, which returned an error:

    Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =,
    !=, <, <= , >, >= or when the subquery is used as an expression.

    SELECT *,
    case city
    WHEN 'London' THEN
    (SELECT shipcity FROM [Sales].[Orders] o WHERE o.custid = c.custid)
      WHEN 'Berlin' THEN
    (SELECT shipcity FROM [Sales].[Orders] o WHERE o.custid = c.custid)
    END
    FROM [TSQLV_3].[Sales].[Customers] c

    ten year old thread, bud. But aside from that, your subquery must have more than one shipcity value, and you can't update something to be more than one value.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

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

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