Updating a name partially on SQL

jaya31
jaya31 used Ask the Experts™
on
Hi All,

I would like to partially change\update the contents of two fields in SQL. Please see image, the fields contain a name but need to re-name only partially where highlighted. For example where is says  

Kirshenbaum Bond Senecal + Partners - OPM Marketing  , would like to change it so that it can read - ABC Company - OPM Marketing. Without touching the info after the dash.

dms-Sql.PNG
There are over 300 rows that need to be changed, would like to test it on one then update both the PRJ_Name and PRJ_DESCRIPT tables. Thanks in advance for you assistance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Kirshenbaum Bond Senecal + Partners - OPM Marketing  , would like to change it so that it can read - ABC Company - OPM Marketing

If so, then you can just use REPLACE condition like this.. in the below code, I've added WHERE clause for you to test the modification on PRJ_ID = 746 first for PRJ_NAME column and then you can do it for both columns for all records..
UPDATE ur_table 
SET PRJ_NAME = REPLACE(PRJ_NAME, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company')
WHERE PRJ_ID = 746

Open in new window

gskTechnology consultant

Commented:
Hi Raja,
For updating 300 rows you need to run 300 update statements ,please open a query analyzer,or whatever tool you
are using. now as you mentioned you have to update two columns, the column names are prj_name and prj_descript(sql generally is case_insensitive).now we have to find unique key value which is prj_id, so on the basis of prj_id we will update 300 rows ,but as in screenshot I am seeing 3 rows ,so telling for 3 rows here, for  rest 297 rows you please change prj_id accordingly.

please run in lower environment first (like dev ,than qa and finally production).kindly take the back up of current table if you need
or like export data as csv and store some wher in hard-drive for future reference.

table_name (put your table name here whatever it is,I given general name)

The query is
====================================================================================================
UPDATE  table_name  set prj_name = REPLACE(prj_name, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') , set  prj_descript=
= REPLACE(prj_descript, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') WHERE PRJ_ID = 746

UPDATE  table_name  set prj_name = REPLACE(prj_name, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') , set  prj_descript=
= REPLACE(prj_descript, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') WHERE PRJ_ID = 760


UPDATE  table_name  set prj_name = REPLACE(prj_name, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') , set  prj_descript=
= REPLACE(prj_descript, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') WHERE PRJ_ID = 770
=============================================================================================
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> I've added WHERE clause for you to test the modification on PRJ_ID = 746 first for PRJ_NAME column and then you can do it for both columns for all records..

To clarify more on what I was trying to say..
1. First try running the UPDATE for only one record to see whether this is what you are expecting or not.
UPDATE ur_table 
SET PRJ_NAME = REPLACE(PRJ_NAME, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company')
WHERE PRJ_ID = 746

Open in new window

2. Once confirmed, then you can run the below query to update all 300 or more records.. No need to write 300 UPDATE statements as mentioned by gsk above..
UPDATE ur_table 
SET PRJ_NAME = REPLACE(PRJ_NAME, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company')
, PRJ_DESCRIPT = REPLACE(PRJ_DESCRIPT, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company')
WHERE PRJ_NAME LIKE 'Kirshenbaum Bond Senecal + Partners%'
OR PRJ_DESCRIPT LIKE 'Kirshenbaum Bond Senecal + Partners%'

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

gskTechnology consultant

Commented:
Sorry, This solution is not for Raja It is for the person who asked the question.I wrongly typed name of Raja
awking00Information Technology Specialist

Commented:
Are you only concerned about the 300 records with the name and description of 'Kirshenbaum Bond Senecal + Partners' or are there others in the remaining 297 that need to be updated (e.g. to XYZ Company, etc.)?

Author

Commented:
Hi All,

Just to clarify, I sent a snip of three rows, but there 300 entries that has to be updated. The company has a name change from
Kirshenbaum Bond Senecal + Partners to ABC Company. However the matter name is different on each entry and has to same.  

Example from snip row 760
Kirshenbaum Bond Senecal + Partners  - OPM Marketing  
to
ABC Company - OPM Marketing

"- OPM Marketing" cannot change in the update in anyway, and as mentioned every row has a different matter name
awking00Information Technology Specialist

Commented:
Are you referring to the portion of the name after the dash as the matter name? If so, we know that should not be changed. Raja Jegan R has provided an update statement that will do just that.

Author

Commented:
Hi All,

Thanks very much for your input, going to run some tests and report back. Thank

Author

Commented:
Hi,

Thanks very much that sql script from Raja Jegan R worked. We had name changes up to 10 records and we used to change it manually, this particular one had 300, this great script to keep. Thank you.

Author

Commented:
Thank you all for you assist with this.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist..
gskTechnology consultant

Commented:
Hi Jaya,

Nice to see that things worked for you ,but uou wanted to update two colums not one ,so why you not run the query

UPDATE  table_name  set prj_name = REPLACE(prj_name, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') , set  prj_descript=
= REPLACE(prj_descript, 'Kirshenbaum Bond Senecal + Partners', 'ABC Company') WHERE PRJ_ID = 770

The complete solution was given by me.please comment if you wish

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial