Complicated (for me) update - 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

General SQL Server Forums

New to SQL Server Programming

Complicated (for me) update

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 10:25:39

Trying to get my head around this:

I have two tables, MNInoDUP, and Arrest_DCSO.
Both have identical data, except MNInoDup contains one entry per person, based on Fname, Mname, Lname, Race, Height, Sex. Each person in Mninodup has a unique MNINO indentifying them. Arrest_DCSO has multiple entries per person, but, for the same person, there are different MNINO's. This is the error which needs to be fixed. So, what i need to do is update the arrest_DCSO table so that the MNINO for each person, matches the MNINO that exists for that same person in the MNInoDup table. In arrest_DCSO, the same person, with the same MNINO, Fname, Mname, Lname, Race, Height, Sex can occur multiple times. in the MNINODUP table, each can and does, occur only once.

Where do i start?

thanks

James

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-12 : 10:30:39

Where do i start?

To make it easy for us you can start with
table structure
sample data
wanted result in relation to sample data

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:31:30

[code]
UPDATE a
SET a.MNINO = m.MNINO
FROM MNInoDUP m
INNER JOIN Arrest_DCSO a
ON a.Fname = m.Fname
AND COALESCE(a.Mname,'') = COALESCE(m.Mname,'')
AND a.Lname = m.Lname
AND COALESCE(a.Race,'') = COALESCE(m.Race,'')
AND COALESCE(a.Height,'') = COALESCE(m.Height,'')
AND COALESCE(a.Sex,'') = COALESCE(m.Sex,'')
WHERE a.MNINO <> m.MNINO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 10:58:10

Thanks visakh. Worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 11:28:53

welcome
I hope you want the duplicates in Arrest_DCSO with same MNINO to be retained else you need to put a delete logic for them to be eliminated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 11:53:36

retaining them in there was the idea, yes. thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 12:31:54

ok...then its fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-14 : 11:26:57

I meant to ask, why use coalesce in this statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 12:45:10

to make sure you can even taking cases where those fields are having NULL values. All operators like >,<,<> etc ignore NULL values as its not internally stored as a value. so in such cases idea is to convert them to default value using COALESCE and then compare

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-14 : 20:03:14

That's what i figured. My boss asked why they were being used and the issue of NULLS was the same answer i gave him. So that being the case, i'm unsure about why he woudn't use them in this case. But then again, everyone around here uses NOLOCK in EVERY script, even though i keep getting cautioned against it by others outside of our circles.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:38:32

quote:
Originally posted by WJHamel

That's what i figured. My boss asked why they were being used and the issue of NULLS was the same answer i gave him. So that being the case, i'm unsure about why he woudn't use them in this case. But then again, everyone around here uses NOLOCK in EVERY script, even though i keep getting cautioned against it by others outside of our circles.

see whats the issue of using NOLOCK especially in OLTP tables

http://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:44:44

WJ...I hope you are taking backups defore all of these updates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 16:19:29

indeed. i live redundantly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:21:15

quote:
Originally posted by WJHamel

indeed. i live redundantly.

consider using snapshot isolation level over NOLOCK

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources