Help with a query

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  April 11, 2005 at 8:57 pm

  #67386

  I have 2 tables with data as.

  Table A

  FName  LName   Value

  John     Brown

  Chris     Smith

  P          DeSouza

  P          DeSilva

  Table B

  FullName

  John Brown

  Chris Smith

  P De Souza

  P De Silva

   

  I am trying to write a query which will update Value column in Table A to 1 if FName and Last Name of a person exists as full Name in Table B. Also something which takes care of the spaces as shown in last 2 rows. Any help will be greatly appreciated.

  TIA

   

   

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  April 11, 2005 at 11:20 pm

  #552023

  use Rtrim funtion

  Select * from Tablea,Tableb Where TableA.Fname + ' ' + TableA.Lname = Tableb.Fullname

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  April 12, 2005 at 2:03 am

  #552056

  What is the reason why you want to keep permanently this redundant information?

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  April 12, 2005 at 7:05 am

  #552121

  Thats how the database is designed initially. query works fine but not for last 2 rows how can those be updated if data matches in both tables.

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  April 12, 2005 at 7:50 am

  #552136

  Try using this:

  Select * from Tablea,Tableb Where TableA.Fname + TableA.Lname = select replace(Tableb.Fullname, ' ', '')

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• HoneyChi

  Ten Centuries

  Points: 1084

  More actions

  April 12, 2005 at 1:34 pm

  #552222

  Hello, ISHANN99:

  With update portion included:

  create table tableB (Fullname varchar (50))

  insert tableB values ('John Brown')

  insert tableB  values ('Chris Smith')

  insert tableB  values ('P De Souza')

  insert tableB  values ('P De Silva')

  create table tableA (fname varchar(25), lname varchar(25), value int)

  insert tableA values ('John', 'Brown',0)

  insert tableA values ('Chris', 'Smith',0)

  insert tableA values ('P', 'DeSouza',0)

  insert tableA values ('P', 'DeSilva',0)

  insert tableA values ('Linda', 'Carpenter',0)

  insert tableA values ('John', 'Doe',0)

  insert tableA values ('Mary', 'LittleLamb',0)

  select * from tableA

  select * from tableB

  update tableA

  set value = 1 where

  REPLACE(fname+lname,' ','')

  in(SELECT REPLACE(fullname,' ','') from tableB)

  select * from tableA

  HTH - Linda