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

Use case and join in a stament Expand / Collapse
Author
Message
Posted Friday, February 22, 2008 6:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 11, 2009 3:24 AM
Points: 90, Visits: 58
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.
Post #459384
Posted Friday, February 22, 2008 7:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 2,393, Visits: 17,963
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
Post #459389
Posted Sunday, February 24, 2008 7:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 11, 2009 3:24 AM
Points: 90, Visits: 58
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.
Post #459505
Posted Thursday, April 9, 2009 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 10:20 AM
Points: 2, Visits: 4
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
Post #694217
Posted Thursday, April 9, 2009 11:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #694297
Posted Thursday, April 9, 2009 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 10:20 AM
Points: 2, Visits: 4
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.

Post #694480
Posted Monday, April 13, 2009 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #695716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse