Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use case and join in a stament


Use case and join in a stament

Author
Message
sebasdoel
sebasdoel
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
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 2Smile). 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
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18647
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
sebasdoel
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
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.
webappguru
webappguru
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
webappguru
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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? Smile)

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
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search