CODEWITHSUNDEEP
sqlsql-update
Paco Hdez
Paco Hdez

Reputation: 1

Update columns in SQL

I have a table in SQL, its name Pagos

Something like this:

Clave  Neto  Val1  Val2
-----------------------    
  01   NULL  NULL     5
  02   NULL     3  NULL
  03      1  NULL  NULL
  04   NULL  NULL  NULL

I want to make something like this, with an SQL Update command:

Update Table1 
Set Neto = 0 WHERE Neto IS NULL, 
    Val1 = 0 WHERE Val1 IS NULL, 
    Val2 = 0 WHERE Val2 IS NULL

Is this possible ?

Upvotes: 0

Views: 71

Answers (2)

jpw
jpw

Reputation: 44881

Assuming MS SQL Server:

update Pagos
set 
    Neto = case when Neto is null then 0 else Neto end,
    Val1 = case when Val1 is null then 0 else Val1 end,
    Val2 = case when Val2 is null then 0 else Val2 end
where Neto is null or Val1 is null or Val2 is null

Instead of case when Neto is null then 0 else Neto endyou could use either isnull(Neto,0)orcoalesce(Neto,0)which does pretty much the same thing by using functions.

The where clause isn't really needed but saves some time by excluding rows where none of the columns is null.

Result:

Clave   Neto    Val1    Val2
01      0       0       5
02      0       3       0
03      1       0       0
04      0       0       0

Upvotes: 1

btidwell
btidwell

Reputation: 467

If your SQL instance supports COALESCE then you could use:

UPDATE Pagos
SET
Neto = COALESCE(Neto, 0),
Val1 = COALESCE(Val1, 0),
Val2 = COALESCE(Val2, 0);

COALESCE(x, y, ...) will return x if x is not null, otherwise it will return y if y not null, and so on down the list.

There are equivalent functions to COALESCE in other versions of SQL. Here is a link to them: http://www.w3schools.com/sql/sql_isnull.asp

Upvotes: 2

Related Questions