tsql update with nested select : easier way?

Question

I got a nested query which I'm trying to run; however I have to screen for numbers with a -1 I have the following:

update invoices set type = 5 where tranno like dbo._fnStripLeadZeroes(( 
   select invoice_number from [bob.jupiter.com].webap.dbo.billinglog)) + '-1'

with invoice_number(varchar15) and tranno(varchar10)

am i approaching this correctly?

Answer 1

This shouldn't be a nested query. What you want is to join the invoices table to the billingLog table to determine which rows to join.

Using the 'update/set/from/where' syntax lets you use a join in your update and it reads nicely.

I used a common table expression [the ;with billing as (..)] part to help simplifiy your query.

Finally, I changed the LIKE to an '=' because you weren't using wildcards, so the like was functioning as an equals anyways.

;with billing as
(
    select dbo._fnStripLeadZeros(invoice_number) + '-1' as invoice_number
    from [bob.jupiter.com].webapp.dbo.billinglog
)
update inv
set inv.type = 5
from invoices inv
inner join billing b
   on (inv.tranno = b.invoice_number  )