February 11, 2009 at 3:54 pm
Hi all,
I'm hoping the collective brilliance can help me out on something. I have a table with 3 columns (ID, Name, Title) and because of how the original data was imported, there are null values right throughout the Name column. For example, The Name column contains strangely enough a persons name and the third column (Title) contains projects they are associated with. The problem is that Name column contains null values right down to the next persons name. For eg.
ID | Name | Title
-----------------------------------------
1 John Change Management
2 null ITC Strategy
3 null Data Centre deployment
4 Brian Cabling contract
What I want to do is fill in all the null values with say 'John' and so on and so forth for each person who is associated with more than one project. There is a massive amount of data that is like this in the table (far too much to change manually) - can anyone provide me with an example script or another post/website to help me fix this swiss cheese data?
February 11, 2009 at 4:14 pm
Are names unique ?
* Noel
February 11, 2009 at 4:19 pm
Sorry, I forgot to mention that - yes the Names column is unique.
February 11, 2009 at 4:21 pm
im assuming your ID column is an identity...using the code below, if you repeat the update several times until no rows are affected, this will work:
[font="Courier New"]
CREATE TABLE EXAMPLE(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name VARCHAR(30),
Title VARCHAR(30))
SET IDENTITY_INSERT example ON
INSERT INTO EXAMPLE(ID,Name, Title)
SELECT 1, 'John','Change Management' UNION
SELECT 2, NULL,'ITC Strategy' UNION
SELECT 3, NULL,'Data Centre deployment' UNION
SELECT 4,'Brian','Cabling contract'
SET IDENTITY_INSERT example OFF
UPDATE EXAMPLE
SET EXAMPLE.NAME = DUPES.NAME
FROM EXAMPLE,EXAMPLE DUPES
WHERE EXAMPLE.ID = DUPES.ID - 1
AND EXAMPLE.NAME IS NULL
AND DUPES.NAME IS NOT NULL
[/font]
Lowell
February 11, 2009 at 4:29 pm
Great - I'll give it a shot now and post back the outcomes.
February 11, 2009 at 4:40 pm
There's one small tweak that needs to be made to the SELECT portion of the script. There is over 20,000 rows in this table and the number of projects (and project names) etc vary per person. So John may have 4 projects he's on, Brian has 1, Mary has 8 etc, so hard coding the SELECT portion isn't really a viable option. Any ideas on how to modify that portion?
February 11, 2009 at 4:40 pm
If the IDs are not consecutive the solution is a bit more convoluted:
CREATE TABLE tb (ID INT , Name VARCHAR(20), Title VARCHAR(200) )
INSERT INTO tb (ID, Name, Title) VALUES(1, 'John', 'Change Management')
INSERT INTO tb (ID, Name, Title) VALUES(2, NULL, 'ITC Strategy')
INSERT INTO tb (ID, Name, Title) VALUES(3, NULL, 'Data Centre deployment')
INSERT INTO tb (ID, Name, Title) VALUES(4, 'Brian', 'Cabling contract' )
INSERT INTO tb (ID, Name, Title) VALUES(5, NULL, 'ITC Strategy')
INSERT INTO tb (ID, Name, Title) VALUES(6, NULL, 'Data Centre deployment')
INSERT INTO tb (ID, Name, Title) VALUES(7, 'You', 'ITC Strategy')
INSERT INTO tb (ID, Name, Title) VALUES(8, 'Me', 'Data Centre deployment')
SELECT * FROM tb
UPDATE t
SET Name = q.Name
FROM tb t
INNER JOIN
(
SELECT Name,
(
SELECT MIN(id)
FROM tb t2
WHERE t2.id > t1.id
AND t2.name IS NULL) AS min_id,
(
SELECT MAX(id)
FROM tb t4
WHERE t4.id < (
SELECT MIN(id)
FROM tb t3
WHERE t3.id > t1.id
AND t3.name IS NOT NULL)
AND t4.name IS NULL
AND t4.id > t1.id ) AS max_id
FROM tb t1
WHERE name IS NOT NULL
) q
ON t.id BETWEEN q.min_id
AND q.max_id
WHERE
q.min_id IS NOT NULL
AND q.max_id IS NOT NULL
SELECT * FROM tb
* Noel
February 11, 2009 at 4:45 pm
nice noel; adding that to my snippet collection.
Lowell
February 11, 2009 at 5:03 pm
Hi Noel,
I'm going to probably show my newbie(ness) here, but I keep getting an error on Line 26
WHERE DirectorsName IS NOT NULL
q
ON t.id BETWEEN q.min_id
'Incorrect syntax near q'
February 11, 2009 at 5:26 pm
forum software ...add a closing parenthesis right before the q...
) q
it took amersand-nbsp-semicolon)
and turned it into this:
😉
Lowell
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply