CODEWITHSUNDEEP
sqlsql-servert-sql
James Wilson
James Wilson

Reputation: 5150

Update table from large select query

I have a table extradata that needs to be updated. It needs to be updated for 863 different accounts.

Here is the query block that returns and puts into a temp table the account numbers that need to be changed.

Is there an easy way to do a bulk update from this query?

declare @tmp table (number int)

insert into @tmp
select accountid as number from letterrequest
where lettercode in ('97001','70003','19998','91009','20000','99100','19997','70002','99099','91008','97002','97210','97231')
and dateProcessed >= '2013-12-04'
union all
select number from gizmo_requests
where letterCode in (97001,70003,19998,91009,20000,99100,19997,70002,99099,91008,97002,97210,97231)
and dateProcessed >= '2013-12-04'
union all
select number from jm_efiling
where letter in (97001,70003,19998,91009,20000,99100,19997,70002,99099,91008,97002,97210,97231)
and datePrinted >= '2013-12-04'

select distinct * from @tmp

This query returns 863 rows of account ID's.

update extradata set line4 = 'TEST'
where extracode = 'L3'
and number in @tmp

only if the number column is in the temp table above.

Upvotes: 0

Views: 94

Answers (2)

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

Try:

update extradata set line4 = 'TEST'
from extradata inner join @tmp T
on extradata.number = T.number
where extracode = 'L3'

You can use joins in update queries

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Use in with a subquery in the where statement:

update extradata
    set line4 = 'TEST'
    where extracode = 'L3' and
          number in (select number from @tmp);

Upvotes: 1

Related Questions