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.  

INTERNAL TABLE
ID       tblid        value
001    001          NULL
002   002           NULL
003   003           NULL

ACTUAL TABLE
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.
LVL 2
CipherISAsked:
Who is Participating?
 
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)
0
 
arnoldCommented:
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.
0
 
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'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arnoldCommented:
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 ............
0
 
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
0
 
arnoldCommented:
Both comments I made pointed to the use of a where clause with the value checked against null.
0
 
arnoldCommented:
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.
0
 
CipherISAuthor Commented:
My solution clearly identified what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.