Update
-
Can someone explain why my UPDATE does not update all rows?
UPDATE ##tblAmounts1 SET AgeBracket = L.AgeBracket
FROM tblSC L INNER JOIN ##tblAmounts1 A ON L.PersonID = A.PersonID
AND A.ClaimDte >= L.StartDate AND A.ClaimDte <= ISNULL(L.EndDate, '2999-12-31')
I have narrowed the problem down to the ISNULL Function.
Yes, the PersonID does exist in the tblSC Table.
-
Your SQL seems to be OK. What do you mean by ALL rows and what makes you think ISNULL is causing you grief? I can only imagine that you want to update the age brackets for claims (which are in your temp table) based on matching the person and some sort of date range... Are you getting any rows updated?
Ahhh - perhaps you should try ISNULL(L.EndDate, '29991231') - take out the dashes.. SQL might have been misinterpreting your date string? To verify, try
print cast('2999-12-31' as datetime)
AND
print cast('29991231' as datetime)
In future, please post much more information and make your code more readable by using fixed width font and, lacking info on the table, have meaningful table aliases and the join conditions in consistent A=L, L=A ordering (one or the other)....
Anyhow, good luck
-
Well, I suppose that the rows are not updated, because the conditions don't include them in the resultset. Without precise information about at least one row that is not updated (supposing some are updated as required) it is nearly impossible to find a solution. Please post at least the values of A.ClaimDte, L.StartDate and L.EndDate in the row that does not work.
I suspect problem could be in time portion of the dates. I know this is pretty obvious, but not having any better clues, it is the only thing that came into my mind. Example:
L.EndDate = '2006.01.01'
A.ClaimDte = '2006.01.01 08:14'
In this example, A.ClaimDte is greater than L.EndDate and the row will not be updated.
HTH, Vladan
-
Ok. Ian Yates, i did try removing the dashes but the same problem occurs.
The problem is that the temp table has 14692 records. When I perfrom the update only 14100 records get updated.
I'm only suspecting the ISNULL Function as I cannot see what else it would be.
Vladan you may be on the right track.
Example,
ClaimDte = '2005-10-01'
StartDate = '2005-10-01 10:44:00'
EndDate = NULL
Not all EndDate's are NULL, some actually have a date like the StartDate.
If the problem is with the time portion of the date, how do we get around it?
-
Change it to IsNull(L.EndDate + 1, '2999-12-31') should do the trick
If not, then try
IsNull(DateAdd(d, 1, L.EndDate), '2999-12-31')
This should still work because NULL + anything = NULL. So adding a day to a null EndDate will still give a NULL result and let your IsNull function work.
-
IMHO the problem is not in the ISNULL or EndDate (at least not in the example you described). It is the time portion.
A.ClaimDte >= L.StartDate is not true in your query, threfore the row is not updated. Solution depends on what are your requirements. If you want to ignore time portions and treat all records from the same day as equal regarding date, then modify the query like this:
UPDATE ##tblAmounts1
SET AgeBracket = L.AgeBracket
FROM tblSC L
INNER JOIN ##tblAmounts1 A ON L.PersonID = A.PersonID
AND A.ClaimDte >= DATEADD(d, DATEDIFF(d, 0, L.StartDate ), 0) AND A.ClaimDte <= ISNULL(L.EndDate, '2999-12-31')
From the data example I suppose, that A.ClaimDate is a date entered manually, and therefore without time portion, whereas StartDate is filled as GETDATE() or similar function and therefore has time portion. If that is true, the abovementioned change should cover all situations. If ClaimDate can sometimes have time portion, you will also have to apply this logic to the last comparision, making it ... AND DATEADD(d, DATEDIFF(d, 0, A.ClaimDte), 0) <= ISNULL(L.EndDate, '2999-12-31').
In both cases, you don't have to bother with the side of each comparision that is to be greater or equal - this part can contain time portion as long as you make sure that the other side does not. DATEADD(d, DATEDIFF(d, 0, A.ClaimDte), 0) >= DATEADD(d, DATEDIFF(d, 0, L.StartDate ), 0) would therefore be overkill... Claim date /with time/, if it is the same day as start date, will always be greater or equal than start date /without time/.
HTH, Vladan
-
You never filter in your script the rows to be updated. That's why it will always update entire table.
Use this:
UPDATE A
SET AgeBracket = L.AgeBracket
FROM tblSC L
INNER JOIN ##tblAmounts1 A ON L.PersonID = A.PersonID
AND A.ClaimDte >= L.StartDate AND A.ClaimDte <= ISNULL(L.EndDate, '2999-12-31')
_____________
Code for TallyGenerator -
It's not the same.
You code: UPDATE ##tblAmounts1
my code UPDATE A
And all you WHERE conditions are applied to A, not to ##tblAmounts1.
_____________
Code for TallyGenerator -
>>How come it works with an alias as oppossed to the actual table name?
It's how the SQL parser correlates what's being UPDATEd with what's in the FROM.
If you alias a table in the FROM, you need to update the alias. Otherwise it treats it as 2 separate instances of the same table, there is no correlation, and every row gets updated.
-
Oops... thanks for catching the error with alias, Sergiy. I concentrated on the conditions too much and overlooked that, just copied it from the original post.
In short, Trigger, if you use alias once, use it everywhere. The table name is sort of "replaced" by the alias, and if you use the true name of the table, it is considered a different table (that's about the same as PW said, just in less expert terms). It works the same in SELECT... as you can see if you run
SELECT TOP 1 anytable.column FROM anytable a
result:
The column prefix 'anytable' does not match with a table name or alias name used in the query.
However, while wrong aliasing often throws such errors, I just tested the UPDATE on a fake temporary table and it is not true that all rows are updated. Somehow, the SQLServer identifies that the table to be updated is the same as mentioned in second part of SQL and updates only those rows that match criteria. I'd like to repeat, that it is not correct and that you have to use aliases throughout the query to ensure it really does what you need... but it works (at least in the below example; I wouldn't be sure it works always as expected).
CREATE TABLE #test (jid int, colid int)
insert into #test values (1,1)
insert into #test values (2,2)
CREATE TABLE #updtest (colid int, someval int)
insert into #updtest values (1,10)
insert into #updtest values (2,15)
UPDATE #updtest
SET someval = 99
FROM #test t
JOIN #updtest u ON u.colid = t.colid
WHERE u.colid = 1
It really updates only the first row.... go figure. I wouldn't trust that such code will work correctly under any circumstances, so I always stick to alias consistently.
-
I know this query is now fixed but I thought a small point on debugging might help in future.
I often find it useful to change such statements into selects just to see what they are actually doing, EG for your original query:
SELECT *
FROM tblSC L INNER JOIN ##tblAmounts1 A ON L.PersonID = A.PersonID
AND A.ClaimDte >= L.StartDate AND A.ClaimDte <= ISNULL(L.EndDate, '2999-12-31')
Even more useful is to expand the resultset using outer joins, for instance:
SELECT *
FROM tblSC L FULL OUTER JOIN ##tblAmounts1 A ON L.PersonID = A.PersonID
AND A.ClaimDte >= L.StartDate AND A.ClaimDte <= ISNULL(L.EndDate, '2999-12-31')
This will return you all the rows with NULLS on either side where there is no match in the join clauses.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply