T SQL Question: How to apply changes to similar rows based on one row's value

In my SQL Server 2008 DB, I have a table with records sort of like this:

ID 1 | Group1 | \ftp\path\group1\file1.txt
ID 2 | Group1 | C:\local\file\path\group1\file1.txt
ID 3 | Group1 | C:\local\file\path\group1\file1.txt
ID 4 | Group1 | C:\local\file\path\group1\file1.txt
ID 5 | Group2 | \ftp\path\group2\file1.txt
ID 6 | Group2 | C:\local\file\path\group2\file1.txt
ID 7 | Group2 | C:\local\file\path\group2\file1.txt

I need to update the table to look like this:

ID 1 | Group1 | \ftp\path\group1\file1.txt
ID 2 | Group1 | \ftp\path\group1\file1.txt
ID 3 | Group1 | \ftp\path\group1\file1.txt
ID 4 | Group1 | \ftp\path\group1\file1.txt
ID 5 | Group2 | \ftp\path\group2\file1.txt
ID 6 | Group2 | \ftp\path\group2\file1.txt
ID 7 | Group2 | \ftp\path\group2\file1.txt

I just don't know how to start this. It's easy for me to find the values in the third column, because they match this wildcard: %:\%.

So, I'm trying to replace the value in those fields that match that wildcard with the correct value in a record that doe开发者_如何学JAVAs not match that wildcard. Damn, it's so hard to explain it.

I'm probably doing a poor job of explaining this issue but the right words are eluding me at the moment.

Any ideas? I appreciate the help.

---

This gets the results you show, but I don't think the rules I applied match the way you described how you got here. You're talking about a wildcard '%:\%' but I see nothing in any of the data that looks anything like that.

DECLARE @foo TABLE
(
    ID VARCHAR(32) PRIMARY KEY, 
    [Group] VARCHAR(32), 
    Val VARCHAR(32)
);

INSERT @foo SELECT   'ID 1','Group1','Value 1'
    UNION ALL SELECT 'ID 2','Group1','Value 2'
    UNION ALL SELECT 'ID 3','Group1','Value 3'
    UNION ALL SELECT 'ID 4','Group1','Value 4'
    UNION ALL SELECT 'ID 5','Group2','A Different Value 1'
    UNION ALL SELECT 'ID 6','Group2','A Different Value 2'
    UNION ALL SELECT 'ID 7','Group2','A Different Value 3';

SELECT ID, [Group], Val FROM @foo;

WITH x AS 
(
    SELECT 
        ID, [Group], Val, 
        rn = ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY val)
    FROM @foo
)
UPDATE x
    SET x.Val = y.Val
    FROM x 
    INNER JOIN x AS y
    ON x.[Group] = y.[Group]
    WHERE y.rn = 1 AND x.rn > 1;

SELECT ID, [Group], Val FROM @foo;

---

Something like this maybe?

UPDATE table
SET table.valueColumn = CT.correctValueColumn
FROM table as CT
    INNER JOIN table as IT on IT.group = CT.group AND CT.valueColumn LIKE '%:\%'
WHERE IT.valueColumn NOT LIKE '%:\%'

I don't have management studio on this machine so I'm not sure it's syntatically correct.

Hope this helps some.