CODEWITHSUNDEEP
sqlsql-server
Phillip Senn
Phillip Senn

Reputation: 47605

UPDATE with isNull

In Microsoft SQL Server,

if the inner select doesn't have a matching criteria, then I need to update Field1 with blank instead of null.

UPDATE Table1
SET Field1=(
    SELECT Field2
    FROM Table2
    WHERE Table2.Field3 = Table1.Field4
)

Upvotes: 0

Views: 1628

Answers (4)

Zorkolot
Zorkolot

Reputation: 2017

The COALESCE() function returns the first non-null expression in a list. You can keep most of your original query, by using COALESCE() to replace any NULL from that query with an empty string.

UPDATE Table1
SET Field1=COALESCE((
    SELECT Field2
    FROM Table2
    WHERE Table2.Field3 = Table1.Field4
), '')

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You can reference another table using SQL Server's UPDATE ... FROM ... syntax:

UPDATE  t1
SET     field1 = COALESCE(t2.field2, '')
FROM    Table1 t1
LEFT JOIN
        Table2 t2
ON      t2.Field3 = t1.Field4

Upvotes: 3

PraveenVenu
PraveenVenu

Reputation: 8337

Try this

UPDATE T1 
SET T1.Field1=isnull(T2.Field2,'')
from Table1 T1 left join Table2 T2
    ON T2.Field3 = T1.Field4

Upvotes: 1

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try like this -

UPDATE a
SET a.Field1 = ISNULL(b.Field2,'')
FROM Table1 a
LEFT JOIN Table2 b ON b.Field3 = a.Field4

Upvotes: 3

Related Questions