Difference Between SQL Statements ..

  • 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

  • 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