JC_Lives
asked on
SQL syntax - complicated where clause
Hi experts,
I have two versions of code and I can't understand why they are different. The first updates the table for several thousand records, and the second only for 33. Can you help me understand why I can't accomplish the same thing in the second version of code? The difference is on lines 8 and 17, I tried to eliminate one where condition in line 8 with a join in line 17. Thanks!!
I have two versions of code and I can't understand why they are different. The first updates the table for several thousand records, and the second only for 33. Can you help me understand why I can't accomplish the same thing in the second version of code? The difference is on lines 8 and 17, I tried to eliminate one where condition in line 8 with a join in line 17. Thanks!!
--First version works in setting the AA_Approved_Date to be the greatest available AA_Approved_Date from aa_approve_to_mail_lmo that is also smaller than the shipped date
update @table
set aa_approved_date = dateadd(d,0,datediff(d,0,aa.approvaldate)),
aa_approve_pool_name = aa.pool_name
from @table t
inner join LMAdhoc.dbo.aa_approve_to_mail_lmo aa
on t.acct# = aa.acct#
where dateadd(d,0,datediff(d,0,aa.approvaldate)) = (select max(dateadd(d,0,datediff(d,0,aa2.approvaldate))) from LMAdhoc.dbo.aa_approve_to_mail_lmo aa2 where aa2.acct# = aa.acct# and (dateadd(d,0,datediff(d,0,aa2.approvaldate)) <= t.shipped_date))
--Second version: Why does this update less values for aa_approved_date?
update @table
set aa_approved_date = dateadd(d,0,datediff(d,0,aa.approvaldate)),
aa_approve_pool_name = aa.pool_name
from @table t
inner join LMAdhoc.dbo.aa_approve_to_mail_lmo aa
on t.acct# = aa.acct#
where dateadd(d,0,datediff(d,0,aa.approvaldate)) = (select max(dateadd(d,0,datediff(d,0,aa2.approvaldate))) from LMAdhoc.dbo.aa_approve_to_mail_lmo aa2 inner join @table t on aa2.acct#=t.acct# where (dateadd(d,0,datediff(d,0,aa2.approvaldate)) <= t.shipped_date))
in the second you are using @table t twice and the second is not the same as first one
to me, second is wrong (logically)...
to me, second is wrong (logically)...
the first has a correlated subquery ... the value of the subquery is related to the current row being processed by the
outer statement.
In the second the subquery is uncorrelated so the value returned is independant of the row being processed.
in the second you are only updating where the date is the max date on the table...
in the first you are updating rows on the table where for that "row set" its date is the maximum...
outer statement.
In the second the subquery is uncorrelated so the value returned is independant of the row being processed.
in the second you are only updating where the date is the max date on the table...
in the first you are updating rows on the table where for that "row set" its date is the maximum...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks lowfatspread, that was super helpful! And just curious, do you happen to have a definition handy for a correlated subquery? Thanks!!
a correlated subquery is one where the values returned are directly related to the row being processed in the outer query
ASKER
cool, thanks
Open in new window
The second has a inner to @table
Open in new window
in the first you make a join in the where with the external query aa to the internal aa2
in the second you make directly the join to the table @table
maybe this is the problem