SQL Update column when there is a null value

I need to update an internal table with a value of a table.  But the one row has a null so I want to pass a default value.  So my internal table is something like this.  

ID       tblid        value
001    001          NULL
002   002           NULL
003   003           NULL

tblid   value
001    Test01
002    Test02
003    NULL

So I want to update the internal table value field with the data from the actual table.  tlbid is the linking field.  on record 003 where it is null i want to use default 'Test01'.  I will hard code that.  I don't want to get it from the table.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You should first update table setting the current null field to whatever it is the default is, then you need to run alter table and change the column by adding the default not null option.

I am not sure what you mean.  To hard code it, you mean you want to periodically run an update query
update [internal table] set value='Test01' where value is NULL?

Configuring the table with the value column as null default 'Test01' will achieve the same goal when value is omitted from the insert/update transactions
but if your code has insert/update with a null into value after the change those transaction will fail with an error if not null is set or if you allow the null option, you'll be in the same position you are now.
CipherISAuthor Commented:
There is no guarantee that that the field will contain a value so i need to code in my SPROC for it.

I originally wrote

update #it1
Set value = (Select Top 1 value from actualtable where tblid = #it1.tblid)

Open in new window

The problem was the result.  row 3 didn't contain a null.  It actually contained 'Test02'.  So, If the Id's match I want to update with the value from the actual table.  

If the actual table is null I just want to set it to default value 'Test01'
One of your checks must include value is NULL.  here you are updating without making sure there is no value set.
add a where clause "where value is null"

The issue the way you are running the proc, the value you are setting will match the ma

what is the purpose of ID in the internal table? You effectively have two tables with the same information

Is your sproc designed to update the internal table with data from the actual table?
After initial synchronization, you could use a trigger on insert, on update and on delete that will maintain the internal table synchronized with the actual table.

the trigger/function can either run as a cursor/or with cts.

WIthout understanding what is you are trying to achieve in the great scheme of things all that can be offered might not be what you are looking to achieve but merely another fix that will require an additional fix later on as well requiring a more complex business process definition that deals with having the same data in multiple tables that make it ever more possible for loss of data integrity as your current example illustrates. i.e. the value in Internal table was not maintained with the actual table value.
The discrepancy might not be noticed until ............
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

CipherISAuthor Commented:
I figured it out.

update #it1
Set value = (Select Top 1 value from actualtable where tblid = #it1.tblid where Value is not null)

update #it1
Set value = (Select 'Test02' value from actualtable where tblid = #it1.tblid where Value is null)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for CipherIS's comment #a40718394

for the following reason:

Solved it
Both comments I made pointed to the use of a where clause with the value checked against null.
I think both my comments http:#a40718335 and http:#a40718349 pointed to the use of a where value is null.

I recommend 2 with the above referenced comments .
The initial post/question included only data example.
No reference to the query.
The sevond including a query lacked a check for where value is null to limit which rows would be updated.
CipherISAuthor Commented:
My solution clearly identified what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.