?
Solved

SQL Query to replace values in columns

Posted on 2016-07-23
4
Medium Priority
?
111 Views
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"



SQL6.jpg
0
Comment
Question by:Member_2_7967487
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
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'
etc.

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!
0
 
LVL 25

Expert Comment

by:chaau
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

0
 
LVL 49

Expert Comment

by:PortletPaul
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"
0
 
LVL 25

Expert Comment

by:chaau
ID: 41728492
Thanks Paul
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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