How to update rows based on data within the same table

• phingers

  Ten Centuries

  Points: 1041

  More actions

  April 29, 2009 at 7:36 am

  #131049

  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!?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 29, 2009 at 7:43 am

  #985911

  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

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  April 29, 2009 at 7:47 am

  #985918

  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]

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  April 29, 2009 at 8:05 am

  #985935

  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

• phingers

  Ten Centuries

  Points: 1041

  More actions

  April 29, 2009 at 8:07 am

  #985937

  both solutions work, many thanks

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  April 29, 2009 at 8:14 am

  #985945

  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]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 29, 2009 at 8:19 am

  #985952

  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