April 29, 2009 at 7:36 am
I have a table called detail which has column bool2 set to 1 for some rows. Some of the rows are related to other rows by their sdetailid e.g. detailid 444699 is related to 440824 because the sdetailid of detailid 444699 is 440824.
Here is the code to create my table and data:
IF OBJECT_ID('TempDB..#detail','U') IS NOT NULL
DROP TABLE #detail
CREATE TABLE [dbo].[#detail](
[detailid] [int] identity NOT NULL,
[detitm] [smallint] NOT NULL,
[bool2] [smallint] NULL,
[detnum] [int] NOT NULL,
[sdetailid] [int] NULL,
CONSTRAINT [pk_detail] PRIMARY KEY CLUSTERED
(
[detailid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #detail ON
INSERT INTO #detail
(detailid, detitm, bool2, detnum, sdetailid)
SELECT '440823','1','0','53214','440823' UNION ALL
SELECT '440824','2','1','53214','440824' UNION ALL
SELECT '444699','3','0','53214','440824' UNION ALL
SELECT '444700','4','0','53214','440823' UNION ALL
SELECT '444724','5','0','53214','440824' UNION ALL
SELECT '444725','6','0','53214','440823' UNION ALL
SELECT '444741','7','0','53214','440824' UNION ALL
SELECT '444742','8','0','53214','440823' UNION ALL
SELECT '444757','9','0','53214','440824' UNION ALL
SELECT '444758','10','0','53214','440823' UNION ALL
SELECT '444761','11','0','53214','440824' UNION ALL
SELECT '444762','12','0','53214','440823' UNION ALL
SELECT '444835','13','0','53214','444741' UNION ALL
SELECT '444836','14','0','53214','444742' UNION ALL
SELECT '444838','15','0','53214','444741' UNION ALL
SELECT '444839','16','0','53214','444742' UNION ALL
SELECT '445072','17','0','53214','444699' UNION ALL
SELECT '445241','18','0','53214','444700' UNION ALL
SELECT '445242','19','0','53214','444699' UNION ALL
SELECT '446116','20','0','53214','444725' UNION ALL
SELECT '446117','21','0','53214','444724' UNION ALL
SELECT '446173','22','0','53214','444724' UNION ALL
SELECT '446174','23','0','53214','444725' UNION ALL
SELECT '446212','24','0','53214','444724' UNION ALL
SELECT '446213','25','0','53214','444725' UNION ALL
SELECT '446254','26','0','53214','444758' UNION ALL
SELECT '446255','27','0','53214','444757' UNION ALL
SELECT '446311','28','0','53214','444757' UNION ALL
SELECT '446312','29','0','53214','444758'
SET IDENTITY_INSERT #detail OFF
I want to update bool2 for any record in detail to whatever is in bool2 for the row identified by its sdetailid
e.g. on detailid 444699 bool2 should be set to 1 because detailid 440824 has that value
I think I need to update the column based on a query that joins detailid to sdetailid but not sure how to do this?
Any help with the code to do this much appreciated!?
April 29, 2009 at 7:43 am
Try something like this:
update D1
set bool2 = D2.bool2
from #detail D1
inner join #detail D2
on D1.sdetailid = D2.detailid
and D1.bool2 != D2.bool2;
- 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 29, 2009 at 7:47 am
Is this what you need?
UPDATE D2
SET
bool2 = D1.bool2
FROM #detail D1
INNER JOIN #detail D2 ON D1.detailID = D2.sdetailid
AND D1.bool2 = 1
Ooops. GSquared beat me to it. 🙂
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 29, 2009 at 8:05 am
Is this....?
Nevermind.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2009 at 8:07 am
both solutions work, many thanks
April 29, 2009 at 8:14 am
They better, they are practically the same code. 😀
I was just surprised that we used the same aliases, but I think GSquared code was closer to the requirements:
I want to update bool2 for any record in detail to whatever is in bool2 for the row identified by its sdetailid
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 29, 2009 at 8:19 am
JacekO (4/29/2009)
Is this what you need?
UPDATE D2
SET
bool2 = D1.bool2
FROM #detail D1
INNER JOIN #detail D2 ON D1.detailID = D2.sdetailid
AND D1.bool2 = 1
Ooops. GSquared beat me to it. 🙂
This version will only set the value to 1, it won't set it to 0 if the sub-value is 0. It will also update rows that are already correct, which can slow it down, and definitely adds unnecessary stuff to the transaction log.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply