Update records in same table

Posted on 2007-07-25
Medium Priority
Last Modified: 2010-03-20
I have the following records in my temporary  table which I have duplicates I am working: I would like the schoolID match.

tempid        fullname            FirstLastName            schoolid      birthdate         ssn              sourceid
35              John H Smith     John smith                  77777          01/0179      999-99-9999    JXX5
89               John Smith       John Smith                  66666          xxxxxx        xxxxxx            FFF7
 this is just an example but I have a 100 pairs in my  temp table and I am looking for a solution to automate the update.
My objective is to make the schoolid match. In here, I would like update tempID 89 to the same schoolid like tempid 35 -77777.  I would prefer sourceid FFF7 follow and match all schoolIds of sourceid JXX5.


Question by:zachvaldez
  • 2
LVL 15

Expert Comment

ID: 19571640
What criteria are you using to consider a record as a duplicate? Just the FirstLastName column?

Author Comment

ID: 19574096
LVL 15

Accepted Solution

derekkromm earned 1000 total points
ID: 19578106
update      t
set      schoolid=t3.schoolid,
from      #tbl t
      join (select firstlastname,min(tempid) tempid from #tbl group by firstlastname) t2 on t.firstlastname=t2.firstlastname
      join (select tempid,schoolid,sourceid from #tbl) t3 on t2.tempid=t3.tempid
where      t.tempid <> t2.tempid

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question