We are renaming our company and this was pretty easy, however we have over 500 location names. (Company Setup/ Company Address) I need to change the name from X to Y so to speak.
I am looking for a way to change location name X to location name Y ( new location name) using SQl , would anyone be able to assist me?
Thank you
Mark
*This post is locked for comments
I ended up using SY00600
and the following
UPDATE SY00600 SET LOCATNNM = Replace(LOCATNNM,'X','Y')
UPDATE SY00600 SET ADRCNTCT = Replace(ADRCNTCT,'X','Y')
Without REPLACE using 'X%' I wad overwriting preceding values and trailing values.
I commit everything to memory so that I could roll back on the fly.
As you mention you want to change company name, you can do it through SQL script.
How you identify you have to update 500 location names ( by 500 location I assume you means on 500 form/window).
Company name save in field CMPNYNAM, so you have to find each table having this field and just need to update the field value.
To find list of tables having this field you can use below script.
Select TABLE_NAME
From INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME ='CMPNYNAM'
before doing anything, don't forget to take backup of your database.
Mark,
With only 500 locations to update I recommend using a macro to automate this. You can merge an excel file with the macro in order to automate the entry for all 500 records. Using a macro means your entries are validated by the GP application and it can save you many headaches down the road.
Regards,
Kirk
would using PSTL work?
Use the replace command something like this
update table set fieldname = replace (fieldname, oldpath, newpath) where ....?
I assume those locations are in the same GP company?
I haven't tested it but I think it's only stored in the SY00600 table in that company database. I think the ID would need to stay the same though.
Share
Report
All responses (
Answers (
11,009