Selective UPDATE based on value in View 2

[RikHess]

I want to update a field in a table (tName_Security.EXPIRATION_DATE) based on external conditions that I have used to build a field in a view (vDBChk_BadSecurityDates_Active.NewExpirationDate).

So far I have a stored procedure designed to process this update, but something is wrong. Since the view does not have a record matching every entry in the table, the ones that aren't in the view are being set to NULL.

I only want the recs in table which match the view to be changed.

Here is what I have so far:

UPDATE tName_Security
 SET EXPIRATION_DATE =(
    SELECT NewExpirationDate
    FROM vDBChk_BadSecurityDates_Active
    WHERE tName_Security.ID = vDBChk_BadSecurityDates_Active.ID
   )

Any help is appreciated. TIA!

 

[monksnake]

I'm not sure what you would want to default set the EXPIRATION_DATE to if it's null, but you can wrap the subquery in a ISNULL command as such:

UPDATE tName_Security
 SET EXPIRATION_DATE = [COLOR=red][b]ISNULL([/b][/color](
    SELECT NewExpirationDate
    FROM vDBChk_BadSecurityDates_Active
    WHERE tName_Security.ID = vDBChk_BadSecurityDates_Active.ID
   )[COLOR=red][b], [i]equivalent data type value as EXPIRATION DATE[/i])[/b][/color]

That at least will keep EXPIRATION_DATE from being NULL.

 

[gmmastros]

UPDATE tName_Security
SET    EXPIRATION_DATE = NewExpirationDate
From   tName_Security
       Inner Join vDBChk_BadSecurityDates_Active
         On tName_Security.ID = vDBChk_BadSecurityDates_Active.ID

If there are no matching records in the view, then the record in tName_Security will not get updated (because of the inner join).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

Thanks, but I may not have been clear.

When I run the SP, the values in the EXPIRATION_DATE for IDs that do not match an ID in the view are being set from a good value to NULL. This is undesirable, are is the basic problem I'm having.

I only want to update the recs that match the view, and leave all others as they are.

 

[monksnake]

I'm sorry I missed the last statement where you said

I only want the recs in table which match the view to be changed.

Do the following

UPDATE tName_Security
 SET EXPIRATION_DATE = ISNULL((
    SELECT NewExpirationDate
    FROM vDBChk_BadSecurityDates_Active
    WHERE tName_Security.ID = vDBChk_BadSecurityDates_Active.ID
   ), EXPIRATION_DATE)

 

[RikHess]

Thanks to both of you. Now my knowlefge is much clearer on how to handle this in the future.