Using a Conditional Update Statement in SQL

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

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?

Conditional UPDATE statement with JOIN

The three where clauses cover all the possible values Days_At_Step could get, so you really don't need a where clause at all. Instead, you can move this logic to a case expression:

UPDATE controlState 
SET    Plan_Status = CASE WHEN Days_At_Step < 2 THEN 1 
                          WHEN (Days_At_Step >= 2 OR Days_At_Step < 4) THEN 2
                          ELSE 3
                     END
FROM   controlState 
JOIN   main ON main.Cscc = controlState.Cscc;

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.

Conditional Update Statement

You can write just one UPDATE here with 9 'setters'. If your numeric values in that temp table are based on (n)varchar you need to take this into account before resetting the actual value. Below I used 0.0001 as an example threshold:

UPDATE
  #fileColumnsChanges
SET
  CompSetOccupancy = IIF(CAST(CompSetOccupancy AS FLOAT) < 0.0001, 0, CompSetOccupancy)
, CompSetADR = IIF(CompSetADR AS FLOAT) < 0.0001, 0, CompSetADR);

Otherwise you can just omit the CAST:

  CompSetOccupancy = IIF(CompSetOccupancy < 0.0001, 0, CompSetOccupancy)

Obviously you have to amend the UPDATE with the other fields.

PS
It helps answering questions like these if you'd show us what is/was in the actual #table.

if condition in sql server update query

The current answers are fine and should work ok, but what's wrong with the more simple, more obvious, and more maintainable:

IF @flag = 1
    UPDATE table_name SET column_A = column_A + @new_value WHERE ID = @ID;
ELSE
    UPDATE table_name SET column_B = column_B + @new_value WHERE ID = @ID;

This is much easier to read albeit this is a very simple query.

Here's a working example courtesy of @snyder: SqlFiddle.

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.

Sql conditional update

You can use conditional logic:

update mytable
    set owner_id = nullif(owner_id, 2),
        security_owner_id = nullif(security_owner_id, 2),
        developer_id = nullif(developer_id, 2)
    where 2 in (owner_id, security_owner_id, developer_id);

You can put this logic either in a trigger or stored procedure or in your application.

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 do i perform a conditional update in SQL with data from multiple tables?

The WHERE clause belongs at the end of the update join statement:

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

See SQL update query using joins for a good canonical answer to your question.

---

Related Topics