May 9, 2012 at 5:27 am
Hi All,
WOuld be grateful if you could help out explaing the difference between the following queries and which one is technically better or correct.
1. This SQL updates more rows
UPDATE t
SET
t.caseno = s.caseno,
t.spellno = s.spellno
FROM
(
SELECT
s.caseno, s.spellno , s.unitno
FROM
dbo.tableA AS t
JOIN
dbo.tableB AS s
ON
t.unitno = s.unitno
WHERE
t.procedure_date BETWEEN
CAST(CAST(s.date_of_admission AS varchar(12)) AS datetime) AND CAST(CAST(s.date_of_discharge AS varchar(12)) AS datetime)
)
s
JOIN tableA t
ON t.unitno = s.unitno
2. This SQL UPDATES less rows:
update t
set t.caseno = s.caseno,
t.spellno = s.spellno
from tableA t
join tableB s on
t.unitno = s.unitno
where t.procedure_date >= cast(cast(s.date_of_admission as varchar(12)) as datetime)
and t.procedure_date <= cast(cast(s.date_of_discharge as varchar(12)) as datetime)
I think the last JOIN outside is creating an increase in the rows being updated?
Thank you
May 9, 2012 at 8:54 am
Without sample data, it's hard to tell what the problem is. Here is what I suspect is happening. Your TableA has multiple procedures for a given unit, some within the date range and some not. The first query selects all procedures for a unit regardless of whether that particular procedure falls within the data range as long as there is some procedure that falls within the date range. The second one only updates procedures where that particular procedure falls within the data range. If the first query had the subquery return the key for TableA and then joined on that key, the results should match.
Which is better or correct depends on your business rules. Since you haven't specified what your business rules are, we can't tell you which matches your business rules.
We do know that the first query will have to access TableA twice: once for the main query and once for the subquery; whereas the second query only has to access TableA once.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply