March 3, 2010 at 3:25 pm
update a
set a.comments1a = b.Subdivision
From Notes a
inner join CowettaCoSubs b
on a.account_id = b.account_id
where a.comments1 is null
This "update" updates nothing
If I run the above w/o the where, it of course updates all records.
What is my problem within the Null logic or where clause.
What I wish to populate is the comments1a field where there are null values in the comments1 field.
Thanks,
Dobe
March 3, 2010 at 3:34 pm
there must not be any null values for a.comments1
March 3, 2010 at 3:37 pm
also not sure if it matters but u dont need the alias in front of the feild ur updating as long as u specify which table ur updating after the update keyword
update a
set comments1a = b.Subdivision --removed the a. from comments1a
From Notes a
inner join CowettaCoSubs b
on a.account_id = b.account_id
where a.comments1 is null
how many records are returned when u run this? I would guess zero.
select *
From Notes a
inner join CowettaCoSubs b
on a.account_id = b.account_id
where a.comments1 is null
March 3, 2010 at 3:39 pm
u should also have the check to see if u dont need to update
update a
set comments1a = b.Subdivision
From Notes a
inner join CowettaCoSubs b
on a.account_id = b.account_id
where a.comments1 is null
and isnull( a.comments1a, '-1') <> isnull( b.Subdivision, '-1') --no sence in updating it if it is already equal. So u check to ensure their not equal.
March 4, 2010 at 5:53 am
First, I'd like to say I like the Doberman. That is my breed, and I lost my best friend almost 3 years ago. She was 7, loyal, very intelligent, and a true companion.
Now to answer you questions. Yes, there are null values in Comments1, and I have run the following code, which results in no data sets returned.
select b.Customer_name, b.subdivision, a.account_id as bAccountId, a.account_id, a.comments1, a.comments1a, a.comments2
from CowettaCoSubs b inner join notes a
on a.account_id = b.account_id
where a.comments1 is null
If I remove the where clause, the result set shows null as well as the remainder of the data.
I even tried to eliminate the nulls first via a CTE, same result.
Dobe
March 4, 2010 at 6:18 am
The only possible reason I can think for this is that you actually have values that are 'NULL' as character value rather than being NULL in the database sense. Do you get the rows returned when you change it to where a.comments1 ='NULL' OR a.comments1 like '%NULL%'?
March 4, 2010 at 6:26 am
That was it. The nulls are not null values, but 'Null'... I didn't see that one comming. I imported this from Access, mabe that is where the problem originated from there, it had been imported from Excel.
Thanks again,
Dobe
March 4, 2010 at 6:28 am
Haven't you looked to see what you're updating?
select a.comments1a, b.Subdivision
From Notes a
inner join CowettaCoSubs b
on a.account_id = b.account_id
where a.comments1 is null
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2010 at 6:44 am
Chris Morris-439714 (3/4/2010)
Apparently not, but thanks for the advise.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply