T-SQL Conditional Update (V2)

T-SQL conditional UPDATE (v2)

The syntax required to create your statement is:

Update [Message] 
SET    [Subject] = CASE WHEN @SubjectChanged = 1 THEN @Subject ELSE [Subject] END,
       Body = CASE WHEN @BodyChanged = 1 THEN @Body ELSE Body END
WHERE  MessageID = @MessageID

if you still want to stick to it after all the suggestions.

N.b. if you leave out the ELSE [Subject] part of the CASE statements, instead of ignoring the UPDATE it sets the field to NULL.

Conditional Update Statement T-SQL

You are just not using the proper syntax for a join and missing an END after each case :

UPDATE SOME_TABLE
     SET Data1 = CASE WHEN Data1 IS NOT NULL THEN  Data0 ELSE Data1 end,
         Data2 = CASE WHEN Data2 IS NOT NULL then  Data1 ELSE Data2 end,
         Data3 = CASE WHEN Data3 IS NOT NULL then  Data2 ELSE Data3 end

Although this update doesn't makes much sense.. if the column is not null, update it and if it is null, keep it null? Are you sure thats what you want to achieve?

T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

You can't use a condition to change the structure of your query, just the data involved. You could do this:

update table set
    columnx = (case when condition then 25 else columnx end),
    columny = (case when condition then columny else 25 end)

This is semantically the same, but just bear in mind that both columns will always be updated. This probably won't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

The only way to do specifically what you're asking is to use dynamic SQL. This is, however, something I'd encourage you to stay away from. The solution above will almost certainly be sufficient for what you're after.

Using a conditional UPDATE statement in SQL

I think what you want is:

UPDATE EMPLOYEE
SET age =
CASE WHEN AGE < 20 THEN 15
ELSE 20 END

Multiple condition update statement in SQL Server

You only need a single case statement. I used = versus in since you aren't supplying multiple values.

update table 
set own=case 
            when own = '1' then '10'
            when own = '2' then '20'
            when own = '3' then '30'
            ....
            else '00' 
        End

How can I improve this conditional UPDATE query?

Split the query in two:

UPDATE t
SET    a = $a$
WHERE  id = $id$

UPDATE t
SET    b = $b$,
       c = $c$
WHERE  id = $id$ AND
       state = $state$

If you need atomicity, wrap in a transaction.

How do i perform a conditional update in SQL with data from multiple tables?

WHERE comes after FROM. I think you want:

UPDATE inv_loc
    SET inv_loc.product_group_id = 'TEMP'
    FROM inv_loc JOIN
         inv_mast_ud
         ON inv_mast_ud.inv_mast_uid = inv_loc.inv_mast_uid
    WHERE inv_mast_ud.eh_spk LIKE '%T';

Note that I changed the LEFT JOIN to an INNER JOIN. You are updating inv_loc, so it makes no sense that that table is the second table in a LEFT JOIN.

I assume you actually want to filter the rows, so a LEFT JOIN is not needed. Otherwise, you would not need inv_mast_ud.

MS SQL - Conditional UPDATE return result

I like the soulution of Gordon, but I do not think you actualy need it.

Simply run the updates in order:

UPDATE BASE_TABLE
SET FLAG='first_table'
where FLAG IS null AND
EXIST (SELECT 1 FROM first_table f1 where f1.ID = ID)

UPDATE BASE_TABLE
SET FLAG='second_table'
where FLAG IS null AND
EXIST (SELECT 1 FROM second_table f2 where f2.ID = ID)

...
And so on.
You dont need to check every row conditionaly, that would be very slow.

Conditional update either column1 or column2 or column3

You need to update all three columns. SET can not update different columns on different rows.

So, pivot your source before joining, then set each column back to its existing value if there is no new value to pick up...

UPDATE
  p
SET
  your_qty  = COALESCE(inv.your_qty , p.your_qty ),
  other_qty = COALESCE(inv.other_qty, p.other_qty),
  my_qty    = COALESCE(inv.my_qty   , p.my_qty   )
FROM
  products p
JOIN
(
  SELECT
    itemNo,
    MAX(CASE WHEN name='your'  THEN qty END) AS your_qty,
    MAX(CASE WHEN name='other' THEN qty END) AS other_qty,
    MAX(CASE WHEN name='my'    THEN qty END) AS my_qty
  FROM
    qty_products_inv
  GROUP BY
    itemNo
)
  inv
    ON p.itemNo = inv.itemNo

Apologies for typos I'm on my phone.

EDIT:

Don't do the following, from my original answer, as @JonArmstrong's comment shows it does not work (unless each row is only ever picking up one single change).

UPDATE
  p
SET
  your_qty  = CASE WHEN inv.name='your'  THEN inv.qty ELSE p.your_qty  END,
  other_qty = CASE WHEN inv.name='other' THEN inv.qty ELSE p.other_qty END,
  my_qty    = CASE WHEN inv.name='my'    THEN inv.qty ELSE p.my_qty    END
FROM
  products p
JOIN
  qty_products_inv inv
    ON p.itemNo = inv.itemNo

Conditional Bulk Update in SQL Server for multiple columns

I don't see value to updating both columns at the same time, so I would suggest something like this:

update people
    set firstname = 'N/A'
    where firstname = 'XXX';

update people
    set lastname = 'N/A'
    where lastname = 'xxx';

If you want to put these in loops, then you can just repeat:

declare @reccnt int;
set @reccent = 1;

while @reccnt > 0
begin
    update top (2000) people
        set firstname = 'N/A'
        where firstname = 'XXX';

    set @reccnt = @@ROWCOUNT;
end;

And for the last name as well.

---

Related Topics