Replace column values

Question

Post reply

Replace column values

Nuts

SSCrazy

Points: 2547
More actions
October 3, 2006 at 3:37 pm

#120837

hi guys
I have a date column in my table.
I need to replace the values in this column to '2099-01-01'(some random value) where the values are null.

I am trying this query but i get an error 'Invalid object value'
UPDATE
Practitioner_Enddate
SET
Practitioner_Enddate = '2099-01-01'
FROM
providerlistforfirstq
where
Practitioner_Enddate is null

Please help guys
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roi Assa SSChasing Mays Points: 611 More actions · Answer 1

what is your table name? Seems that you wrote column name instead table name.

Are you sure you need the query above and not this one:

UPDATE providerlistforfirstq

SET

Practitioner_Enddate = '2099-01-01'

where Practitioner_Enddate is null

Kindest Regards,

Roi Assa

Nuts SSCrazy Points: 2547 More actions · Answer 2

Hi sorry that was a typo!

I tried this

UPDATE providerlistforfirstq

SET

Practitioner_Enddate = '2099-01-01'

where Practitioner_Enddate is null

but it says '0 rows affected'

I have got null values in the ' Practitioner_Enddate' column in my table and it is a datetime field.

How to solve it???

thanks

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

I have got null values in the ' Practitioner_Enddate' column in my table and it is a datetime field.

Are you sure? What do you get if you run the following?

SELECT *
FROM ProviderListForFirstQ
WHERE Practitioner_EndDate IS NULL

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

If you have defined the column as Datatime Datatype and if you are seeing NULL values in the column then it should be returning some values for the query posted by Jeff and should also update the values properly as per Roi Assa's post. And even if you are inserting a blank it would be converted implicitly to '1900-01-01 00:00:00.000' , so check you datatype of the column.

Prasad Bhogadi
www.inforaise.com