Updating a name partially on SQL

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.
jaya31Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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

0
gskTechnology consultantCommented:
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
=============================================================================================
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

gskTechnology consultantCommented:
Sorry, This solution is not for Raja It is for the person who asked the question.I wrongly typed name of Raja
0
awking00Information Technology SpecialistCommented:
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.)?
0
jaya31Author 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
0
awking00Information Technology SpecialistCommented:
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.
0
jaya31Author Commented:
Hi All,

Thanks very much for your input, going to run some tests and report back. Thank
0
jaya31Author 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.
0
jaya31Author Commented:
Thank you all for you assist with this.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to assist..
0
gskTechnology consultantCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.