SQL Query to replace values in columns

Posted on 2016-07-23
Medium Priority
Last Modified: 2016-07-25
Hi, I would like to know how to write a query that would search all the columns and replace the names.

Example per screenshot, I would like to do the following below. I am not sure if it is ideal to create an Index called, "Replacement" and based on the column that matches to an entry from the Index, then do certain replacement. Thanks!

* Search Category 1 - Category 6 columns
* If "Furniture > Consoles" then replace with "Consoles"
* If "Sale > All Sale > Furniture" then replace with "Sale"
* If "More Themes > French Decor > French Furniture" then replace with  "Theme > French Furniture"

Question by:Member_2_7967487
  • 2
LVL 43

Accepted Solution

pcelba earned 2000 total points
ID: 41725749
You have to do it column by column, value by value:

UPDATE <YourTable> SET  <Column1> = 'Consoles' WHERE <Column1> = 'Furniture > Consoles'
UPDATE <YourTable> SET  <Column2> = 'Consoles' WHERE <Column2> = 'Furniture > Consoles'
UPDATE <YourTable> SET  <Column3> = 'Consoles' WHERE <Column3> = 'Furniture > Consoles'

You have to provide correct table and column names instead of <name> into above command.

Of course, you may create a script/stored procedure which could build the UPDATE commands dynamically for all columns you are intending to update but that's more work...

Don't forget to backup your database as the first step!
LVL 25

Expert Comment

ID: 41726922
Just to correct the previous expert. You can do this using a single query, if required. The query in this case will use a CASE statement for each of the columns, like this:

UPDATE tbl1 SET  Column1 = 
CASE WHEN Column1 = 'Furniture > Consoles' THEN 'Consoles' 
     WHEN Column1 = 'Sale > All Sale > Furniture' THEN 'Sale'
     WHEN Column1 = 'More Themes > French Decor > French Furniture' THEN 'Theme > French Furniture'
     ELSE Column1 END,
Column2 = 
CASE WHEN Column2 = 'Furniture > Consoles' THEN 'Consoles' 
     WHEN Column2 = 'Sale > All Sale > Furniture' THEN 'Sale'
     WHEN Column2 = 'More Themes > French Decor > French Furniture' THEN 'Theme > French Furniture'
     ELSE Column2 END,
Column3 = 
CASE WHEN Column3 = 'Furniture > Consoles' THEN 'Consoles' 
     WHEN Column3 = 'Sale > All Sale > Furniture' THEN 'Sale'
     WHEN Column3 = 'More Themes > French Decor > French Furniture' THEN 'Theme > French Furniture'
     ELSE Column3 END
-- etc. Continue for other columns

Open in new window

LVL 49

Expert Comment

ID: 41727459
a tiny "correction": a single query approach would use "case expressions"

"case statements" are branching mechanisms usually associated with stored procedures or functions (yes I know; I'm being "pedantic")

but also note:
the term"expression" applies to something that evaluates to a single value, hence "case expression"
LVL 25

Expert Comment

ID: 41728492
Thanks Paul

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question