delete linking records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete linking records

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-12 : 07:27:39

Hello everyone,

I have to delete duplicate name for one lawyer. However, there are many cases assigned to that lawyer in two different id. I want to delete one, but move cases from delete record to the other one. I wonder how I could do that won't loose assigned cases from delete lawyer, and move those cases to the other.

For example:
List1:
LawyerID= 127332
LawyerName= Allan Gal
There are two cases assigned to this lawyerid (111222 and 222333)

List2:
LawyerID=127333
lawyerName= allan Galimore
There are also two cases assigned to this lawyerID (444555, and 555666)

I would like to delete lawyer name listed in list1 and move two cases: 111222 and 222333 to lawyer listed in list2 since Lawyername in list2 is the correct name. How would I do and not loosing the cases assigned to that lawyer name.
The above information came from two tables:

First Table structure:
LawyerID=127313
lawyerName= Allan Gal
Address= Ste 306, phila, pa 15222

Second table structure:
CaseID=111222
LawyerID= 127313

Thanks a bunch...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 08:29:53

update t2 set
lawyerid = 127333
from <table2> as t2
where Lawyerid = 127332

delete table1 where lawyerid = 127332

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 08:38:03

What is "List 1" and "List 2"?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Update first all cases with "wrong" lawyer id to correct lawyer id.
then remove wrong lawyer id from table.

E 12°55'05.63"
N 56°04'39.26"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-12 : 08:58:00

quote:
Originally posted by Peso

What is "List 1" and "List 2"?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Update first all cases with "wrong" lawyer id to correct lawyer id.
then remove wrong lawyer id from table.

E 12°55'05.63"
N 56°04'39.26"

At first, I thought the same thing, but lawyerid is assigned automatic by system. So I won't able to modify them. I think I will go with TG 's solution. I have not try yet..but I think that will solve the problem.

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-12 : 09:14:45

quote:
Originally posted by TG

update t2 set
lawyerid = 127333
from <table2> as t2
where Lawyerid = 127332

delete table1 where lawyerid = 127332

Be One with the Optimizer
TG

Thanks TG...the query worked very well. It updated the record as I wanted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources