SQL update DeviceNumPlanMap

Unanswered Question
Jan 20th, 2009

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'



I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
stephan.steiner Wed, 01/21/2009 - 01:58

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')


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'

sebastien_michelet Wed, 01/21/2009 - 09:11

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



This Discussion