Running an update on all GP Companies

I have come across situations wherein, I had to apply the exact 3 line SQL update to a single table for all the companies available in a single GP instance.

When required to switch the payables setup flag found under

Setup>>Purchasing>>Payables>>>Defaults>Default Summary View

from Amounts Since Last Close to

calendar year for all the 67 companies, the below script can be used to accomplish the same.

--------------------begin

select INTERID
INTO #COMPANIES
FROM    DYNAMICS..SY01500

SELECT 'UPDATE '+RTRIM(LTRIM(INTERID)) +'.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3'
from    #companies

--------------------end

 This will yield results in my case it yielded 46 companies as shown below

----Resulting SQL

UPDATE TWO.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp1.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp3.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp4.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp5.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp7.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp11.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp15.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp16.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp17.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp19.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp20.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp27.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp40.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp42.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp43.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp45.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp57.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp58.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp61.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp62.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp63.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp65.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp66.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp67.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp68.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp69.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp70.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp71.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp72.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp73.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp74.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp75.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp76.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp131.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp132.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp133.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp134.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp135.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp136.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp137.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp138.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp139.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp140.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp141.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
UPDATE cmp142.DBO.PM40100 SET DateSensitivitySumView =3 where DateSensitivitySumView <> 3
---END

The resulting SQL can then be executed to accomplish the requirement.

Thanks for reading my blog.