How can I update multiple columns with a Replace in SQL server?

Question

How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server

I want to do this but the value exists in multiple columns of the same type. The values are foreign keys varchars to an employee table. Each column represents a task, so the same employee may be assigned to several tasks in a record and those tasks will vary between records. How can I do this effectively? Basically something of a replace all accross varying columns throughout a table.

Thanks for any help or advice.

Cheers, ~ck in San Diego

Vercingetorix

This would be an indication that your DB is not noramlised? (i.e. in 3NF) – Barong

I am more of a c# dev so I don't know the inner workings of the database. I am curious tho about how you would represent that relationship correctly? If each record in a table represented a project, and each column represents a task, and an employee can be assigned to one or more tasks. Where is the duplication? How can I remedy this? Please advise. – Vercingetorix

I think you should give an example of what you have and what you want to change. It's difficult to formulate a SQL query without having a good idea of what the data looks like. – Landahl

Typically your foreign keys are int and not varchars. This way you do not have this type of an issue. This way a single employee record can be linked to multiple tasks and you just use the ID of the employee. You could also have a cross reference table that links employees to tasks as well. This means 3 tables and not just 2, but it is a common thing. – Canaliculus

tEmpNum jEmpNum yEmpNum xEmpNum 0 15059 0 15059 13456 13456 13456 13456 15059 15059 15059 15059 15059 15059 15059 15059 15059 15059 15059 15059 -- Say I wanted to update every occurrence of 15059 to 13673? Although these are numeric, many of the values contain text like RH6754 and what not. Thanks. :) – Vercingetorix

Yes Steve I know about the int, but in theory as long as the values are unique and it is enforced, then there is really no problem having a varchar as a primary key. See Northwind Customers table 'ALFKI' lol. – Vercingetorix

Answer 1

This should do the trick:

UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
    field2 = replace(field2, 'oldstring2', 'newstring2')

etc...

Bandylegged

and its ok if the string doesn't exist in the column? ok this sounds cool. I would just apply it accross all possible columns??? Thanks for the tip. i will try this. Sweet! – Vercingetorix

Please don't try it right out on the production system (or at least do a backup first). – Bandylegged

Also, you can use a where clause to narrow things down a lot (probably). – Bandylegged

Just to update any interested parties; I ran into a bit of an issue with this. The datatype is varchar(7), if 'newstring' is same length or shorter as 'oldstring' all is well, but when I was trying to go the other direction, data will be truncated errors reared their ugly head. A less effecient way came to mind and that was to run an update on each column in the stored proc like Begin Update table1 SET field1='newstring' WHERE field1='oldstring' END, then do the same query for field2, field3, filedn. I am sure it is not effecient, but the result is what I was seeking. Thanks all! – Vercingetorix

Answer 2

The main idea is to create a SQL Update sentence, no matter how many fields has the table. It was created on SQL Server 2012, however I think it works on 2008 too.

Sample table:

CREATE TABLE SampleTable
(
    Field1 INT,
    Field2 VARCHAR(20),
    Field3 VARCHAR(20),
    Field4 VARCHAR(100),
    Field5 DATETIME,
    Field6 NVARCHAR(10)
);

Get only varchar and nvarchar fields. Change OLD_TEXT and NEW_TEXT accord to your requirement. Change system_type_id values if you need match not only varchar and nvarchar fields.

SELECT 'UPDATE dbo.SampleTable SET ' + STUFF((SELECT ', [' + name + '] =   REPLACE([' + name + '], ''OLD_TEXT'', ''NEW_TEXT'')' 
FROM sys.COLUMNS
WHERE 
    [OBJECT_ID] = OBJECT_ID('SampleTable')
    AND [is_identity] = 0 --It's not identity field
    AND [system_type_id] in (167, 231) -- varchar, nvarchar
FOR XML PATH('')), 1,1, '')

The result of the last query is:

UPDATE dbo.SampleTable SET  
    [Field2] = REPLACE([Field2], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field3] = REPLACE([Field3], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field4] = REPLACE([Field4], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field6] = REPLACE([Field6], 'OLD_TEXT', 'NEW_TEXT');

just copy the result and execute in SSMS. This snippet saves you a little time when writing the update sentence.

Hope it helps.

Feriga

awesome solution! – Eros

Answer 3

In answer to the poster's supplementary question about how to normalize this data structure. Here's how you'd do it:

Project
-------
ProjectID
ProjectName
etc...

Employee
--------
EmployeeID
EmployeeName
etc...

Task
----
TaskID
ProjectID
EmployeeID
TaskDescription
etc...

Your current structure, where you have a bunch of Task1, Task2, etc... columns in the Project table, was clearly not designed by somebody that understands relational databases.

During the process of firing that individual, you might explain that his design violates the First Normal Form, while directing him to the "Repeating groups across columns" section of that linked article.

Peters

Ah, then all bets are off :) For what it's worth though, this is a much bigger deal than "a little denormalized". It's the First Normal Form that's being violated, not the 3rd. There are circumstances that call for flattening tables in a way that duplicates data, but that's not what's happening here. This is just pain generation for no benefit. – Peters

Answer 4

I know it has been 12 years, but I recently had a similar requirement and this is how I solved it (after having no luck in finding a proper full solution anywhere). The difference was that I had to change the values in ALL of the database tables that contained a certain string value. The following procedure is more general, and can be used for one table too.

CREATE OR ALTER PROCEDURE UPDATE_ALL_COLUMNS
    @TableNameSearchFilter NVARCHAR(100),
    @TableSchema NVARCHAR(100),
    @TestValue NVARCHAR(100),
    @NewValue NVARCHAR(100)
AS
BEGIN
    
    DECLARE @NRCOLUMNS INT;
    DECLARE @i INT = 0;
    DECLARE @COLUMN NVARCHAR(100) = '';
    DECLARE @SQL NVARCHAR(MAX) = '';
    DECLARE @TableToUpdate NVARCHAR(256) = '';
    DECLARE @insertingNULL BIT;

    IF (@NewValue IS NULL) SET @insertingNULL = 1
    ELSE SET @insertingNULL = 0;

    WHILE @TableToUpdate IS NOT NULL
    BEGIN
        SELECT @TableToUpdate = MIN(TABLE_NAME)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME LIKE @TableNameSearchFilter
            AND TABLE_SCHEMA = @TableSchema
            AND TABLE_NAME > @TableToUpdate;
            
        WITH CTE1 AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @TableToUpdate
                AND TABLE_SCHEMA = @TableSchema                     
                AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
        )
        SELECT @i = MIN(RN), @NRCOLUMNS = MAX(RN) FROM CTE1;

        WHILE (@i <= @NRCOLUMNS AND @TableToUpdate IS NOT NULL)
        BEGIN
            WITH CTE AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS RN
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = @TableToUpdate
                    AND TABLE_SCHEMA = @TableSchema                     
                    AND (@insertingNULL = 0 OR (@insertingNULL = 1 AND IS_NULLABLE = 'YES'))
            )
            SELECT @COLUMN = COLUMN_NAME 
            FROM CTE
            WHERE RN = @i;

            SET @SQL = @SQL + 
                N'UPDATE D SET ' + @COLUMN + N' = ' + ISNULL(N'''' + @NewValue + N'''', N'NULL')
                + N' FROM ' + @TableSchema + N'.' + @TableToUpdate + N' D WHERE CAST(D.' + @COLUMN + ' AS NVARCHAR) = ' + ISNULL(N'''' + @TestValue + N'''', N'NULL') + ';'
                + NCHAR(13) + NCHAR(10);

            SET @i = @i + 1;
        END;        
    END;    

    --PRINT SUBSTRING(@SQL, 1, 4000)
    --PRINT SUBSTRING(@SQL, 4001, 8000)
    --PRINT SUBSTRING(@SQL, 8001, 12000)
    --PRINT SUBSTRING(@SQL, 12001, 16000)
    --PRINT SUBSTRING(@SQL, 16001, 20000)
    --PRINT SUBSTRING(@SQL, 20001, 24000)
    EXEC (@SQL)
END
GO

As a usage example:

EXEC UPDATE_ALL_COLUMNS '%temp%', 'dbo', '', NULL

Parameters:

• @TableNameSearchFilter - this will be used with the LIKE operator to find all the tables from your database whose names that match this value;
• @TableSchema - the schema of the table (usually dbo)
• @TestValue - the value to search for in ALL of the columns (and rows) of each found table;
• @NewValue - the value to replace @TestValue with. Can also be NULL.

Explanation:

• The EXEC statement will find ALL tables whose names contain the word 'temp', on the 'dbo' schema of your database, then search for the value '' (empty string) in ALL columns of ALL of the found tables, then replace this value with a NULL.
• Obviously, if you have long(er) column/table names or the update value, make sure to update the limits on the parameters.
• Make sure to first comment the last line (EXEC (@SQL)) and uncomment the lines with PRINT, just to get an idea for what the procedure does and how the final statements look like.
• This is not going to work (most likely) if you want to search for the NULL value (i.e. to have @TestValue as NULL). Nevertheless, it can be easily changed to accomplish this as well, by replacing the equal sign from the WHERE clause (in the dynamic query) with IS NULL and removing the rest of the line, when @TestValue IS NULL.
• The procedure accounts for inserting NULL values, and will only do so in NULLABLE columns.

Hope this helps someone eventually.

Gusman