Combine this SQL update operation that deletes all unqualified items and enables all qualified items?

Question

I am performing the following intentions:

I have in my query a list/table of voucher_codes that are effectively qualified. I want to mark all student_vouchers that are in this list as deleted = 0 and is_applied is marked true, if they appear in the list.

For all other student_vouchers under the given foreign key student_header_id of a student_header table that do not appear, i want to mark these as deleted with applied false.

The following code performs well enough, however I seem to be encountering plenty of deadlocks in regards to other transactions interacting with student_vouchers. So I think it would be better if I can merge this into a single update operation, is that possible? And would such change help fix the deadlock situation?

DECLARE @applied_voucher_table table
                                       (
            value nvarchar(max)
                                       );    
-- REMOVE INVALID STUDENT VOUCHERS
UPDATE student_vouchers
    SET is_applied   = 0,
        deleted      = 1,
        deleted_date = GETDATE(),
        edited_date = GETDATE()
    OUTPUT INSERTED.voucher_code INTO @DeletedVouchers
    WHERE student_header_id = @HEADERID AND deleted = 0;

DECLARE @AppliedVouchers TABLE (voucher_code NVARCHAR(MAX));
IF (
                (
                    SELECT COUNT(*)
                    FROM @applied_voucher_table
                    WHERE value IS NOT NULL
                    AND value != '') > 0
                )
BEGIN
                --UPDATE VALID VOUCHERS
    UPDATE student_vouchers
        SET is_applied   = 1,
            deleted      = 0,
            edited_date  = GETDATE(),
        OUTPUT INSERTED.voucher_code INTO @AppliedVouchers
        WHERE student_header_id = @HEADERID
          AND voucher_code IN
             (SELECT value
              FROM @applied_voucher_table
              WHERE value IS NOT NULL AND value != '')
          AND id IN (SELECT id
              FROM @applied_voucher_table);
 END;

student_vouchers (
  id,
  student_header_id,
  voucher_code,
  is_applied,
  deleted,
  deleted_date,
  edited_date

Let me know if there is something unclear! Note that it is impotant that i receive this list @DeletedVouchers, however it would be good to also have the @AppliedVouchers list, which are both sent to a remote process.

Answer 1

You can combine the two tables and updates into one, by using a left-join and some CASE or IIF expressions.

DECLARE @UpdatedVouchers TABLE (voucher_code NVARCHAR(MAX) NOT NULL, is_deleted bit NOT NULL);

UPDATE sv
SET is_applied   = IIF(avt.id IS NOT NULL, CAST(1 AS bit), CAST(0 AS bit)),
    deleted      = IIF(avt.id IS NOT NULL, CAST(0 AS bit), CAST(1 AS bit)),
    deleted_date = IIF(avt.id IS NOT NULL, sv.deleted_date, GETDATE())
    edited_date = GETDATE()
OUTPUT inserted.deleted, inserted.voucher_code
INTO @UpdatedVouchers (voucher_code, is_deleted)
FROM student_vouchers sv
LEFT JOIN @applied_voucher_table av
    ON avt.value = sv.voucher_code
   AND avt.value != ''
   AND avt.id = sv.id
WHERE sv.student_header_id = @HEADERID
  AND (sv.deleted = 0 OR avt.id IS NOT NULL);

Note that the semantic in the left-join is slightly different from the original, but I suspect this is more correct anyway. In the original, the id and value could be matched from different rows, where it looks like it should have been an EXISTS iwth two inner WHERE conditions. In this version, they needs to come from one row.