| Author |
 Topic  |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
 Posted - 03/12/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/12/2012 : 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
India
47023 Posts |
Posted - 03/12/2012 : 10:31:30
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 03/12/2012 : 10:58:10
|
| Thanks visakh. Worked perfectly. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/12/2012 : 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
USA
614 Posts |
Posted - 03/12/2012 : 11:53:36
|
| retaining them in there was the idea, yes. thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/12/2012 : 12:31:54
|
ok...then its fine
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 03/14/2012 : 11:26:57
|
| I meant to ask, why use coalesce in this statement? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/14/2012 : 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
USA
614 Posts |
Posted - 03/14/2012 : 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
India
47023 Posts |
Posted - 03/15/2012 : 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 |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 03/15/2012 : 16:19:29
|
indeed. i live redundantly.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/15/2012 : 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/
|
|
|
| |
Topic |
|
Complicated (for me) update
