updating a column only if not null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating a column only if not null

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-13 : 15:39:20

Hi All
Iam trying to update a column called resource using a stored procedure if and only if the column has a null value in it.

Else it should not update the column.
Getting errors with the below SP.What am i doing wrong below?

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[StepTwo_DBupdates]
(
@RQID int=null,
@RDID int=null,
@TAT int = null)
AS
UPDATE    REPORTSELECTION
SET        tat = @TAT,
	   Resource = COALESCE(NULLIF(Resource,NULL),(Select RI.resource from ReportInfo RI WHERE (reqid = @RQID) AND (rdesc = @RDID) )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 15:43:40

you've not specified any relationship between the tables REPORTSELECTION and ReportInfo. how are they related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-13 : 15:48:32

They are related with the feild namedrdesc

I want to update the Resource feild in REPORTSELECTION table with a value in resource feild in ReportInfo Table

quote:
Originally posted by visakh16

you've not specified any relationship between the tables REPORTSELECTION and ReportInfo. how are they related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 15:52:32

How do you think you could update without specifying the condition?


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[StepTwo_DBupdates]
(
@RQID int=null,
@RDID int=null,
@TAT int = null)
AS
UPDATE    rs
SET        rs.tat = @TAT,
	   rs.Resource = COALESCE(RI.Resource,rs.Resource)
FROM REPORTSELECTION rs
INNER JOIN ReportInfo RI 
ON RI.namedrdesc = rs.namedrdesc
WHERE (reqid = @RQID) 
AND (rdesc = @RDID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-08-14 : 08:30:03

For some reasons this is not working.We dont need to have a join between these two tables in this case.We just want to insert/update the resource feild value in REPORTSELECTION table with a value in resource feild in ReportInfo Table.

I tried with the above and getting undesired results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:26:01

quote:
Originally posted by jim_jim

For some reasons this is not working.We dont need to have a join between these two tables in this case.We just want to insert/update the resource feild value in REPORTSELECTION table with a value in resource feild in ReportInfo Table.

I tried with the above and getting undesired results

without valid criteria how do you determine what rows to be insert/updated? whats the meaning of doing such a DML operation?
perhaps you could explain your scenario better for us to understand.
Current scenario doesnt make much sense to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources