SQL : Update value when the value in the database is null

Question

I know this is already asked question and possible to be close. But i really want a answer, I already searched through the internet, Read documentations, Blogs, and Question to SO.

This is my Query so Far,

declare @count numeric
select @count = (select count(1) from E496_TitleReference a where
exists (select 1 from #tempTransactions b where  a.EPEB_RoD = b.tEPEB_RoD  and 
a.EPEB_ENO = b.tEPEB_ENO and a.EPEB_ID = b.tEPEB_ID and a.Title_Seq = b.tTitle_Seq))

update E496_TitleReference
set PrintStatus = '{0}',Is_AESM=isnull(-1,Is_AESM)
from E496_TitleReference a where
exists (select 1 from #tempTransactions b where  a.EPEB_RoD = b.tEPEB_RoD  and 
a.EPEB_ENO = b.tEPEB_ENO and a.EPEB_ID = b.tEPEB_ID and a.Title_Seq = b.tTitle_Seq)

if @@rowcount <> @count
begin
     rollback tran
     Print "Error: There is an error on table E496_TitleReference."
     return
end
go

For eg, In my table in Database i have column name Is_AESM, In Is_AESM column it have 4 values.

Is_AESM
NULL
NULL
-1
-2

Something like this. Now when i run my script, it has no problem when i run it,

 Is_AESM=isnull(-1,Is_AESM)

In this query it will detect if Is_AESM is null, it will update Is_AESM = -1 if not it will retain the value.

Now my problem is, if my query detect Is_AESM has a null value, it will update all the value to -1.

Is_AESM
-1
-1
-1
-1

The result is something like that. Now i want is update only the null value not all the value in column Is_AESM.

I think this query is wrong Is_AESM=isnull(-1,Is_AESM).

Any ideas will be a big help.

Is_AESM=IF(Is_AESM = " ",-1,Is_AESM) ?

DPS
– DPS

2018-10-23 05:25:43 +00:00
Commented Oct 23, 2018 at 5:25 — DPS
– DPS, Commented Oct 23, 2018 at 5:25

Fahmi · Accepted Answer · 2018-10-23 05:24:54Z

2

You may try with coalsece() function

update E496_TitleReference
set PrintStatus = '{0}',Is_AESM=coalsece(Is_AESM,-1)
from E496_TitleReference a where
exists (select 1 from #tempTransactions b where  a.EPEB_RoD = b.tEPEB_RoD  and 
a.EPEB_ENO = b.tEPEB_ENO and a.EPEB_ID = b.tEPEB_ID and a.Title_Seq = b.tTitle_Seq)

answered Oct 23, 2018 at 5:24

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serkan Arslan · Accepted Answer · 2018-10-23 06:02:45Z

1

you need to replace order of parameters.

Is_AESM=isnull(Is_AESM, -1)

answered Oct 23, 2018 at 6:02

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Comments

Maadesh Sivakumar · Accepted Answer · 2018-10-23 05:45:21Z

0

You can use COALSECE function. It returns the first non-null entry from the given list. So:

Is_AESM= COALSECE(IS_AESM,-1)

This will return IS_AESM value if it is not null (since it is the first non-null value)

Else if IS_AESM is NULL then it returns -1 (since it is the non-null value)

answered Oct 23, 2018 at 5:45

Maadesh Sivakumar

3676 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL : Update value when the value in the database is null

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related