February 22, 2008 at 6:20 pm
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.
February 22, 2008 at 7:56 pm
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
February 24, 2008 at 7:01 am
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.
April 9, 2009 at 10:46 am
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
April 9, 2009 at 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 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
April 9, 2009 at 3:33 pm
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.
April 13, 2009 at 6:52 am
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
March 13, 2019 at 9:07 am
GSquared - Thursday, April 9, 2009 11:44 AMYou 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 Table2on Table1.Col2 = Table2.Col2and Table1.Col1 = 2left outer join Table3on Table1.Col2 = Table3.Col2and Table1.Col1 = 3;orselect *,case Col1when 2 then(select Col3from Table2where Col2 = Table1.Col2)when 3 then(select Col3from Table3where 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?
March 13, 2019 at 9:22 am
GSquared - Thursday, April 9, 2009 11:44 AMYou 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 Table2on Table1.Col2 = Table2.Col2and Table1.Col1 = 2left outer join Table3on Table1.Col2 = Table3.Col2and Table1.Col1 = 3;orselect *,case Col1when 2 then(select Col3from Table2where Col2 = Table1.Col2)when 3 then(select Col3from Table3where 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
March 13, 2019 at 9:37 am
busterDimartino - Wednesday, March 13, 2019 9:22 AMGSquared - Thursday, April 9, 2009 11:44 AMYou 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 Table2on Table1.Col2 = Table2.Col2and Table1.Col1 = 2left outer join Table3on Table1.Col2 = Table3.Col2and Table1.Col1 = 3;orselect *,case Col1when 2 then(select Col3from Table2where Col2 = Table1.Col2)when 3 then(select Col3from Table3where 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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply