SQL Server, How to perform a bulk update of certain fields within single table

Question

SQL Server, How to perform a bulk update of certain fields within single table (orders)?

order no	comp date	order notes
1000579	01/01/1900	NULL
1000578	01/01/1900	NULL
1005077	01/01/1900	NULL
1000576	01/01/1900	NULL
1000575	01/01/1900	NULL
1000574	01/01/1900	NULL
1000573	01/01/1900	NULL
1000572	01/01/1900	NULL
1000571	01/01/1900	NULL

I wish to bulk Update above (not all from the column) the following order numbers comp (completion) date field and order notes field

order completion date

1000579 = 14/08/2020
1000578 = 29/08/2019
1005077 = 02/06/2019
1005706 = 03/06/2019
1005750 = 29/06/2019
1000574 = 25/06/2019
1000573 = 24/07/2019
1000572 = 24/07/2019
1000571 = 03/07/2019

order notes

1000579 = "bulk update"
1000578 = "bulk update"
1005077 = "bulk update"
1000576 = "bulk update"
1000575 = "bulk update"
1000574 = "bulk update"
1000573 = "bulk update"
1000572 = "bulk update"
1000571 = "bulk update"

I have the following to successfully update the first order number, but I need a way to update multiple records. I do not know which method to use

UPDATE dbo.orders o
SET o.comp_date = '2020-08-14', o.order_notes= 'bulk update'
WHERE o.order_no = 1000579;

Any advice much appreciated

Expecting to see a single SQL query I have several thousands of records to update, above is just a small sample

Answer 1

Couple minor things here. First, not all the order numbers in your example updates exist in your source data. Thats probably just a typo, so no biggie, but that's why in my final output those rows wind up not being updated.

Second, I had to switch around the dateformat because of where I live, but again, that's not really material to the solution, just making you aware of the fact that I did it.

Basically you need to get the rows you want to use for your update in some table-like structure. That can be a temporary table (table beginning with a #), a permanent table (if you think you'll need to do something like this over and over again) or, as I've done here, a Common Table Expression (aka CTE) (which is basically just defining a set of rows that only exists for the query which follows it. Load the CTE up with the row constructors you want to update, then join your orders table with the CTE and perform your update.

Several thousand rows should be just find to union all together in a CTE like this. If you get up into, say, 10-100 thousand rows, you might want to look into something like SSIS, BCP, or the Import/Export wizard to load the rows into a persisted table.

/*****************************
Simulate Orders Table 
(pretending this is your persisted table you want to update)
*****************************/
drop table if exists #orders
create table #orders
(
    OrderNo int not null,
    CompDate date not null default '01/01/1900',
    OrderNotes varchar(1000) null
)

insert into #orders
(
    OrderNo
)
values
    (1000579),
    (1000578),
    (1005077),
    (1000576),
    (1000575),
    (1000574),
    (1000573),
    (1000572),
    (1000571)

/*****************************
Do the Update
*****************************/
;with src 
(
    OrderNo,
    CompDate,
    OrderNotes
)
as
(
    select 1000579, '08/14/2020', 'bulk update'
    union all select 1000578, '08/29/2019', 'bulk update'
    union all select 1005077, '06/02/2019', 'bulk update'
    union all select 1005706, '06/03/2019', 'bulk update'
    union all select 1005750, '06/29/2019', 'bulk update'
    union all select 1000574, '06/25/2019', 'bulk update'
    union all select 1000573, '07/24/2019', 'bulk update'
    union all select 1000572, '07/24/2019', 'bulk update'
    union all select 1000571, '07/03/2019', 'bulk update'
)
update t
set CompDate = s.CompDate,
    OrderNotes = s.OrderNotes
from #orders t
inner join src s
    on t.OrderNo = s.OrderNo

select top 1000 *
from #orders