Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL syntax - complicated where clause

Posted on 2011-09-23
7
Medium Priority
?
279 Views
Last Modified: 2012-05-12
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!!
--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))

Open in new window

0
Comment
Question by:JC_Lives
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 36589954
the first use a where

where    aa2.acct# = aa.acct#
                                                                    and ( dateadd(d, 0, datediff(d, 0, aa2.approvaldate)) <= t.shipped_date )
                                                         )

Open in new window


The second has a inner to @table

inner join @table t on aa2.acct# = t.acct#
                                                           where    ( dateadd(d, 0, datediff(d, 0, aa2.approvaldate)) <= t.shipped_date )

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
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36590029
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)...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36593079
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...

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 36593092
you'd need to write the second like this for it to be equivalent to the first

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 t1 
         on aa2.acct#=t1.acct# 
      where (dateadd(d,0,datediff(d,0,aa2.approvaldate)) <= t1.shipped_date)
       and aa2.acct#=aa.acct#
       and t.shipped_date=t1.shipped_date
     )

Open in new window

0
 

Author Closing Comment

by:JC_Lives
ID: 36598850
Thanks lowfatspread, that was super helpful! And just curious, do you happen to have a definition handy for a correlated subquery? Thanks!!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36601387
a correlated subquery is one where the values returned are directly related to the row being processed in the outer query
0
 

Author Comment

by:JC_Lives
ID: 36601535
cool, thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question