SQL Server - Update columns with CASE based on inner join when table I'm joining on has many more rows than table I'm updating

Question

I'm working on a way to update the status of an order in my system based upon the status of the order's invoice headers. Currently, the below query does this, however it does it multiple times (once per invoice header). All invoice headers on an order will have the same status, so I'm looking for a way to restrict this to just checking one invoice header.

UPDATE [MyServer].[MyDatabase].[dbo].[Orders]
SET Status = (
        CASE 
            WHEN ih.InvoiceStatus = 'ReadyToPrint'
                THEN 4
            WHEN ih.InvoiceStatus = 'Transferred'
                THEN 5
            END
        )
FROM [MyServer].[MyDatabase].[dbo].[Orders] ord
INNER JOIN Database2.dbo.invoiceheader ih
    ON ih.OrderHeaderNumber= ord.OrderNumber
WHERE ord.Status = 2

SAMPLE DATA: Currently if I do a SELECT on Status rather than a set, I get either a 4 or a 5 outputted for each and every invoice header. I'd like to find a way to restrict this to just one per order.

Invoice headers have a relationship to orders based upon the invoice header's OrderHeaderNumber and the order's OrderNumber, which is what I'm matching on for the Inner Join.

SAMPLE Invoice Header Rows: Below are two example invoice headers for a hypothetical order.

OrderHeaderNumber - InvoiceNumber - HeaderCharge - InvoiceStatus
12345             - 129           - 200          - ReadyToPrint
12345             - 128           - 350          - ReadyToPrint

Answer 1

You can use a variety of styles to get a single value. I prefer this method as you can select and get an exact listing of what will be updated.

UPDATE ord SET Status = ih.InvoiceStatus
--SELECT *
FROM [MyServer].[MyDatabase].[dbo].[Orders] ord
INNER JOIN (
    SELECT DISTINCT
        OrderHeaderNumber
        ,CASE 
            WHEN ih.InvoiceStatus = 'ReadyToPrint'
                THEN 4
            WHEN ih.InvoiceStatus = 'Transferred'
                THEN 5
         END AS InvoiceStatus
    FROM Database2.dbo.invoiceheader
) ih ON ih.OrderHeaderNumber= ord.OrderNumber
WHERE ord.Status = 2