Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Community Member

SQL update DeviceNumPlanMap

Does anybody knows how to write a successful SQL query to update a field in the DeviceNumPlanMap table, for example the external phone mask. it needs to be derived from the DN from numplan table and the device name from the device table.

I tried this without success:

update deviceNumPlanMap set E164Mask='9001112222' where DeviceNumPlanMap.fknumplan = numplan.pkid and DeviceNumPlanMap.fkdevice = device.pkid and numplan.dnorpattern='1000' and device.name='SEP001E4A925A13'

thanks,

-Sebastien

2 REPLIES

Re: SQL update DeviceNumPlanMap

How about

update deviceNumPlanMap set E164Mask='9001112222' where fknumplan = (select pkid from numplan where dnorpattern = '1000') AND fkdevice = (select pkid from device where name = 'SEP001E4A925A13')

Alternatively

update deviceNumPlanMap set E164Mask='9001112222' from devicenumplanmap dnmp inner join device dev on dev.pkid = dnmp.fkdevice and dev.name = 'SEP001E4A925A13' inner join numplan np on dnmp.fknumplan = np.pkid and np.dnorpattern = '1001'

Community Member

Re: SQL update DeviceNumPlanMap

thanks Stephan, the first option did the trick just fine.

The second option with inner join returned a syntax error, i think i read somewhere that inner joins where not permitted in update statements.

anyway, thanks a lot

-Sebastien

613
Views
5
Helpful
2
Replies
CreatePlease to create content