CUCM SQL Query to Update External Phone Number Mask of Device Pool

Guzmanjl — Sun, 17 Mar 2019 19:16:50 GMT

Hello All,

I am trying to run a Update SQL query for an entire Device Pool which consist of about 80 devices in total.

I can easily update the e164mask for all of the devices or for an specific Directory Number or range of Directory Numbers.

My problem is associating the devicenumplanmap table with the devicepool table in the Update query.

I have tested multiple SQL queries with no luck. Here are some of the ones I have put together from the help of other discussion from the Cisco Support Community and ran in my lab.

1. run sql update devicenumplanmap set e164mask = '89898989' where (fkdevice in (select pkid from device where (fkdevicepool in (select pkid from devicepool where name = 'ORPDXMAN')))

A syntax error has occurred.

2. run sql update devicenumplanmap set e164mask = '89898989' where devicenumplanmap.fkdevice = device.pkid and device.fkdevicepool = devicepool.pkid and devicepool.name = 'ORPDXMAN'

Column (device) not found in any table in the query (or SLV is undefined).

3. run sql update devicenumplanmap set e164mask = '8989898' where inner join device.fkdevicepool = devicepool.pkid on device.pkid = devicenumplanmap.fkdevice and devicepool.name = "ORPDXMAN'

A syntax error has occurred.

I am pretty sure my 3rd query is not even close to it been correct. My 1st query is the one I feel I am the closest to getting the result that I need.

Any help is always appreciated.

Re: CUCM SQL Query to Update External Phone Number Mask of Device Pool

Guzmanjl — Fri, 23 Feb 2018 20:32:03 GMT

Haha wow, this is what happens when you stare at something too long. I was only missing a close bracket ) at the end of my first query.

1. run sql update devicenumplanmap set e164mask = '89898989' where (fkdevice in (select pkid from device where (fkdevicepool in (select pkid from devicepool where name = 'ORPDXMAN')))

Needs 4 )))) at the end.

topic CUCM SQL Query to Update External Phone Number Mask of Device Pool in IP Telephony and Phones

CUCM SQL Query to Update External Phone Number Mask of Device Pool

Re: CUCM SQL Query to Update External Phone Number Mask of Device Pool