Update Row for same ID

• PSB

  SSChampion

  Points: 13195

  More actions

  December 3, 2018 at 6:46 am

  #382383

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  December 3, 2018 at 7:05 am

  #2015299

  Something like this?

  UPDATE wrong
SET  names = correct.names
FROM
   #exceptions wrong
JOIN #exceptions correct ON wrong.Id = correct.id
WHERE
   wrong.Names   = 'Generic'
   AND correct.Names <> 'Generic';

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  December 3, 2018 at 7:27 am

  #2015302

  PSB - Monday, December 3, 2018 6:46 AM

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

  May be this

  
  update exp1 set exp1.id=exp1.id,exp1.names=(
  case when exp1.id=exp2.id and exp1.names=exp2.names then exp1.names else
  exp2.names end)
  from #Exceptions exp1 inner join #Exceptions exp2
  on exp1.id=exp2.id
  where exp2.names<>'Generic'

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  December 3, 2018 at 7:27 am

  #2015303

  PSB - Monday, December 3, 2018 6:46 AM

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

  Really sorry system slow . deleted the post

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  December 3, 2018 at 7:27 am

  #2015304

  PSB - Monday, December 3, 2018 6:46 AM

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

  Really sorry system slow . deleted the post

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  December 3, 2018 at 7:27 am

  #2015305

  PSB - Monday, December 3, 2018 6:46 AM

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

  Really sorry system slow . deleted the post
  

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  December 3, 2018 at 7:27 am

  #2015306

  PSB - Monday, December 3, 2018 6:46 AM

  Hi,

  I have a table that holds data where for some of the identical IDs the names are different . One real name and the other comes as Generic .I want to update the row with the person's real name where IDS are same and Name is Generic

  CREATE TABLE #Exceptions

  (

  ID INT,

  Names VARCHAR(100)

  )

  INSERT INTO #Exceptions ( ID,Names )

  SELECT 1,'Generic'

  UNION ALL

  SELECT 1, 'John Kelley'

  UNION ALL

  SELECT 2, 'Hello World'

  UNION ALL

  SELECT 3, 'Hello World2'

  UNION ALL

  SELECT 3, 'Generic'

  SELECT * FROm

  #Exceptions

  --Correct Result

  SELECT 1 AS ID,'John Kelley' AS Names

  UNION ALL

  SELECT 1 AS ID, 'John Kelley' AS Names

  UNION ALL

  SELECT 2 AS ID, 'Hello World' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  UNION ALL

  SELECT 3 AS ID, 'Hello World2' AS Names

  DROP TABLE #Exceptions

  Thanks,
  PSB

  Really sorry system slow . deleted the post
  

  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 3, 2018 at 7:32 am

  #2015310

  Hold the phone a minute... why do you have two tables that you must maintain the name in?  That's not "normalization" and having to do such maintenance is contrary to just about every sane practice I'm aware of for databases.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• PSB

  SSChampion

  Points: 13195

  More actions

  December 3, 2018 at 8:26 am

  #2015322

  Thanks, Works perfectly.

• PSB

  SSChampion

  Points: 13195

  More actions

  December 3, 2018 at 11:25 am

  #2015346

  It is one table .