Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: How can I update a value on a column only if that value is null?

I have an SQL question which may be basic to some but is confusing me.

Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood

Let's say that I input the row:

121312, Rayna, Pieterson, BMW123d, Brown, NULL, NULL

Now I want to update the values for this person, but only if the new value is not null, Update:

121312, Rayna, Pieterson, NULL, Blonde, Fanta, NULL

The new row needs to be:

121312, Rayna, Pieterson, BMW123d, Blonde, Fanta, NULL

So I was thinking something along the lines of:

Update Person(PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood) set Car = @Car (where @Car is not null), HairColour = @HairColour (where @HairColour...)... etc.

My only concern is that I can't group all the conditions at the end of the query because it will require all the values to have the same condition. Can't i do something like Update HairColour if @HairColour is not Null

like image 222
Tamara JQ Avatar asked Apr 20 '10 14:04

Tamara JQ


1 Answers

Set the column equal to itself with an isnull round it setting it to your parameter.

UPDATE
    YourTable
SET
   YourColumn = ISNULL(YourColumn, @yourParameter)
WHERE
    ID = @id
like image 186
Robin Day Avatar answered Nov 02 '22 23:11

Robin Day