October 1, 2009 at 12:44 am
there are two tables
Survey_Thank_You_Page_Config
acct_idsurvey_stub allow_invitation_forward_flag
157F2E213-BC6B-48CA-B025-1597688A8F96
12091B6B5-CE91-4110-93D7-311FF642731F
192264A07-3626-42CB-88CA-476DFB8B3BBC
1F5903819-A158-4F7C-A712-5292A86081D8
acct_idsurvey_stub page_detail_valuepage_detail_id
157F2E213-BC6B-48CA-B025-1597688A8F961 202
12091B6B5-CE91-4110-93D7-311FF642731F5 203
192264A07-3626-42CB-88CA-476DFB8B3BBC1 202
1F5903819-A158-4F7C-A712-5292A86081D81 202
UPDATE stypc SET
stypc.allow_invitation_forward_flag =
CASE WHEN spd.page_detail_id = 202 THEN spd.page_detail_value ELSE stypc.allow_invitation_forward_flag END
FROM Survey_Thank_You_Page_Config as stypc
INNER JOIN dbo.Survey_Page_Detail as spd
ON stypc.acct_id = spd.acct_id AND stypc.survey_stub = spd.survey_stub
WHERE stypc.acct_id = 1
In above query , the "allow_invitation_forward_flag" column of 1st table should be updated with value "1" but its not working
please help
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2009 at 2:24 am
I don't understand your problem. It's working on my test data.
DECLARE @Survey_Thank_You_Page_Config TABLE (
acct_id int,
survey_stub uniqueidentifier,
allow_invitation_forward_flag bit
)
DECLARE @Survey_Page_Detail TABLE (
acct_id int,
survey_stub uniqueidentifier,
page_detail_value int,
page_detail_id int
)
INSERT INTO @Survey_Thank_You_Page_Config (acct_id, survey_stub) SELECT
1, '57F2E213-BC6B-48CA-B025-1597688A8F96' UNION SELECT
1, '2091B6B5-CE91-4110-93D7-311FF642731F' UNION SELECT
1, '92264A07-3626-42CB-88CA-476DFB8B3BBC' UNION SELECT
1, 'F5903819-A158-4F7C-A712-5292A86081D8'
INSERT INTO @Survey_Page_Detail (acct_id, survey_stub, page_detail_value, page_detail_id) SELECT
1, '57F2E213-BC6B-48CA-B025-1597688A8F96', 1, 202 UNION SELECT
1, '2091B6B5-CE91-4110-93D7-311FF642731F', 5, 203 UNION SELECT
1, '92264A07-3626-42CB-88CA-476DFB8B3BBC', 1, 202 UNION SELECT
1, 'F5903819-A158-4F7C-A712-5292A86081D8', 1, 202
UPDATE stypc
SET stypc.allow_invitation_forward_flag = CASE WHEN spd.page_detail_id = 202
THEN spd.page_detail_value
ELSE stypc.allow_invitation_forward_flag
END
FROM @Survey_Thank_You_Page_Config as stypc
INNER JOIN @Survey_Page_Detail as spd
ON stypc.acct_id = spd.acct_id
AND stypc.survey_stub = spd.survey_stub
WHERE stypc.acct_id = 1
--Displays data updated as requested ---> what's wrong?
SELECT * FROM @Survey_Thank_You_Page_Config
-- Gianluca Sartori
October 1, 2009 at 2:31 am
yeah, i couldnt see anything wrong with it either
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 2:43 am
My bad 🙁
i gave wrong data
here is the second table's data
INSERT INTO @Survey_Page_Detail (acct_id, survey_stub, page_detail_value, page_detail_id) SELECT
1, '57F2E213-BC6B-48CA-B025-1597688A8F96', 1, 202 UNION SELECT
1, '2091B6B5-CE91-4110-93D7-311FF642731F', 5, 203 UNION SELECT
1, '57F2E213-BC6B-48CA-B025-1597688A8F96', 1, 202 UNION SELECT
1, '57F2E213-BC6B-48CA-B025-1597688A8F96', 1, 202
here u will find repitition of second column data (survey_stub)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2009 at 2:54 am
Still can't find anything wrong.
I suggest you try a SELECT first, so you can identify what's wrong.
SELECT stypc.*, spd.*,
newFlag = CASE WHEN spd.page_detail_id = 202
THEN spd.page_detail_value
ELSE stypc.allow_invitation_forward_flag
END
FROM @Survey_Thank_You_Page_Config as stypc
INNER JOIN @Survey_Page_Detail as spd
ON stypc.acct_id = spd.acct_id
AND stypc.survey_stub = spd.survey_stub
WHERE stypc.acct_id = 1
-- Gianluca Sartori
October 1, 2009 at 4:32 am
now check with this data,
i think there is some problem with join
---------------------------------------
DECLARE @Survey_Thank_You_Page_Config TABLE (
acct_id int,
survey_stub uniqueidentifier,
allow_invitation_forward_flag bit
)
DECLARE @Survey_Page_Detail TABLE (
acct_id int,
survey_stub uniqueidentifier,
page_detail_value int,
page_detail_id int
)
INSERT INTO @Survey_Thank_You_Page_Config (acct_id, survey_stub) SELECT
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7'
INSERT INTO @Survey_Page_Detail (acct_id, survey_stub, page_detail_value, page_detail_id) SELECT
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',0,202 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',0,505 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',4,504 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',4,503 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',5,502 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',2,501 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',5,203 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',1,202 union select
1,'0C23194A-2A5E-4EEB-9050-930A59E953A7',0,201
UPDATE stypc
SET stypc.allow_invitation_forward_flag = CASE WHEN spd.page_detail_id = 202
THEN spd.page_detail_value
ELSE stypc.allow_invitation_forward_flag
END
FROM @Survey_Thank_You_Page_Config as stypc
INNER JOIN @Survey_Page_Detail as spd
ON stypc.acct_id = spd.acct_id
AND stypc.survey_stub = spd.survey_stub
WHERE stypc.acct_id = 1
--Displays data updated as requested ---> what's wrong?
SELECT * FROM @Survey_Thank_You_Page_Config
---------------------------------------------------------------------------------
I need "1" value instead of "NULL" in allow_invitation_forward_flag column.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2009 at 4:51 am
If you change your update to a select you can see what is happening
DECLARE @Survey_Thank_You_Page_Config TABLE (
acct_id int,
survey_stub uniqueidentifier,
allow_invitation_forward_flag bit
)
DECLARE @Survey_Page_Detail TABLE (
acct_id int,
survey_stub uniqueidentifier,
page_detail_value int,
page_detail_id int
)
INSERT INTO @Survey_Thank_You_Page_Config (acct_id, survey_stub) SELECT
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7'
INSERT INTO @Survey_Page_Detail (acct_id, survey_stub, page_detail_value, page_detail_id) SELECT
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 202 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 505 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 4, 504 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 4, 503 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 5, 502 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 2, 501 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 5, 203 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 1, 202 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 201
select * ,
willupdate = CASE WHEN spd.page_detail_id = 202
THEN spd.page_detail_value
ELSE stypc.allow_invitation_forward_flag
END
FROM @Survey_Thank_You_Page_Config as stypc
INNER JOIN @Survey_Page_Detail as spd
ON stypc.acct_id = spd.acct_id
AND stypc.survey_stub = spd.survey_stub
WHERE stypc.acct_id = 1
acct_id survey_stub allow_invitation_forward_flag acct_id survey_stub page_detail_value page_detail_id willupdate
----------- ------------------------------------ ----------------------------- ----------- ------------------------------------ ----------------- -------------- -----------
1 0C23194A-2A5E-4EEB-9050-930A59E953A7 NULL 1 0C23194A-2A5E-4EEB-9050-930A59E953A7 0 201 NULL
1 0C23194A-2A5E-4EEB-9050-930A59E953A7 NULL 1 0C23194A-2A5E-4EEB-9050-930A59E953A7 0 202 0
1 0C23194A-2A5E-4EEB-9050-930A59E953A7 NULL 1 0C23194A-2A5E-4EEB-9050-930A59E953A7 0 505 NULL
So should the value be null because of page_detail_id 505 and 201 or 0 because of 202 ?
October 1, 2009 at 5:02 am
according to query
the value for page_detail_id = 202 is 1 which is correct , but we apply same logic iin UPDATE query
the answer is NULL but i need 1
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2009 at 5:10 am
But according to your query NULL is a equally valid value to update the column to.
I dont know enough about what you are attempting to do but are you needing the allow_invitation_forward_flag column to be updated to the ,for example, MAX value of what i have called 'willupdate' column ?
October 1, 2009 at 5:19 am
Maybe something like this ?
DECLARE @Survey_Thank_You_Page_Config TABLE (
acct_id int,
survey_stub uniqueidentifier,
allow_invitation_forward_flag bit
)
DECLARE @Survey_Page_Detail TABLE (
acct_id int,
survey_stub uniqueidentifier,
page_detail_value int,
page_detail_id int
)
INSERT INTO @Survey_Thank_You_Page_Config (acct_id, survey_stub) SELECT
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7'
INSERT INTO @Survey_Page_Detail (acct_id, survey_stub, page_detail_value, page_detail_id) SELECT
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 202 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 505 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 4, 504 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 4, 503 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 5, 502 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 2, 501 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 5, 203 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 1, 202 union select
1, '0C23194A-2A5E-4EEB-9050-930A59E953A7', 0, 201;
with cteMaxValue(acct_id,survey_stub ,willupdate)
as
(
select acct_id,survey_stub ,willupdate =max(
CASE WHEN page_detail_id = 202
THEN page_detail_value
END)
FROM @Survey_Page_Detail
WHERE acct_id = 1
group by acct_id,survey_stub
)
update stypc
set allow_invitation_forward_flag = willupdate
from @Survey_Thank_You_Page_Config as stypc
join cteMaxValue
on cteMaxValue.acct_id = stypc.Acct_id
and cteMaxValue.survey_stub = stypc.survey_stub
SELECT * FROM @Survey_Thank_You_Page_Config
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply