468,121 Members | 1,405 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,121 developers. It's quick & easy.

Finding and Correcting Missing Column data in a single table

Good afternoon folks, a quick question for all you SQL gurus out there. I'm cleaning up a table from another person at my company and am running into a small problem.


Table: T1
Field1 - String, not unique
Field2 - String, not unique

No PK is defined.

I have 58,000 some odd entries in T1, there are other columns but I'm only concerned with these two. There is no unique identifier in this table (it's being migrated into SQL and that hasn't been decided on yet).

Some of the records have initials input into Field2 (such as BA, DO, ZK, etc..). There are multiple entries for the same value in Field1. When I need to do is find all the records where Field1 is the same but Field2 differs, then update Field2 so that all the Field1 records have the same Field2 records. I.e. this:

Existing data:
12345 DK
12345 DK
12345 NULL
12345 PD

Data after fix:
12345 DK
12345 DK
12345 DK
12345 DK

Can someone point me in the right direction to resolve this? The engine is SQL Server 2000.
Nov 14 '07 #1
1 1240
1,017 Expert 512MB
To find such records do following:

Expand|Select|Wrap|Line Numbers
  1. Select Field1, count(distinct isnull(Field2,))
  2. From table
  3. Group by Field1
  4. Having count(distinct isnull(Field2,)) > 1
How do you determine what to update Field2 is it a minimum not null value?
Nov 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Aaron W. West | last post: by
6 posts views Thread by Maxi | last post: by
6 posts views Thread by Hemant Shah | last post: by
5 posts views Thread by maury | last post: by
18 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.