Query training -- Complex queries

Can anyone please suggest a book (or a training class) that gets into
the guts of complex queries? I need to understand, for example, the
internal differences between this update query:
Update PositionsEOM
Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date
and this one:
Update PositionsEOM
Set MktVal = Derived.MktVal From
(Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date) As Derived
The second example ran for 20 minutes and I cancelled it; the first one
finished in 2.5 minutes. You wouldn't KNOW that you could write "Set"
expressions like the first example, from ANY of the online
documentation. Even though the second example uses a derived table, I
don't see much of a logical difference between them, and would not
expect the run time to be different.
Also, I decided that when you mix inner and outer joins in a select,
which I do sometimes, if you change the order, the result set WILL be
different (not just the time it takes). (I think this is true...) When
you build complicated select statements in Enterprise Manager's view
designer window, it doesn't really take this into account (you can say
"Select all rows from tableA", but you might really be getting all rows
from the result of the inner join on tableA and tableB).
I found one online article that mentioned that if you mix inner and
outer joins in a query, you CAN control the order of the joins -- and it
shows various ways to do that, including parentheses and derived or
virtual tables. But only ONE online article. It's VERY rare to see any
of this documented, and SQL Books Online sure doesn't show
examples.......
The examples for Update queries usually show that you can set price to
price*1.05, and helpfully explains that this will increase all prices by
5 percent. That's trivial. Then they move on to other subjects...
I don't know where to come across such learning; SQL Books Online sure
doesn't go into these kinds of examples. Their syntax diagrams are
probably complete, but of course they are huge and somewhat hard to
understand.
Any help will be greatly appreciated.
David WalkerYou might want to check out _Advanced Transact-SQL For SQL Server_ by Itizik
Ben-Gan and Tom Moreau. Pretty good book.
As for your queries, the first one filters the rows to update with the JOIN.
The second one will update every single row in the PositionsEOM table once
for every row returned in the derived table, as there is no correlation
between the two tables (you have not JOINed them.)
"DW" <None> wrote in message news:u#t6Wj##DHA.3272@.TK2MSFTNGP09.phx.gbl...
> Can anyone please suggest a book (or a training class) that gets into
> the guts of complex queries? I need to understand, for example, the
> internal differences between this update query:
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> and this one:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> The second example ran for 20 minutes and I cancelled it; the first one
> finished in 2.5 minutes. You wouldn't KNOW that you could write "Set"
> expressions like the first example, from ANY of the online
> documentation. Even though the second example uses a derived table, I
> don't see much of a logical difference between them, and would not
> expect the run time to be different.
> Also, I decided that when you mix inner and outer joins in a select,
> which I do sometimes, if you change the order, the result set WILL be
> different (not just the time it takes). (I think this is true...) When
> you build complicated select statements in Enterprise Manager's view
> designer window, it doesn't really take this into account (you can say
> "Select all rows from tableA", but you might really be getting all rows
> from the result of the inner join on tableA and tableB).
> I found one online article that mentioned that if you mix inner and
> outer joins in a query, you CAN control the order of the joins -- and it
> shows various ways to do that, including parentheses and derived or
> virtual tables. But only ONE online article. It's VERY rare to see any
> of this documented, and SQL Books Online sure doesn't show
> examples.......
> The examples for Update queries usually show that you can set price to
> price*1.05, and helpfully explains that this will increase all prices by
> 5 percent. That's trivial. Then they move on to other subjects...
> I don't know where to come across such learning; SQL Books Online sure
> doesn't go into these kinds of examples. Their syntax diagrams are
> probably complete, but of course they are huge and somewhat hard to
> understand.
> Any help will be greatly appreciated.
> David Walker|||DW (None) writes:
> Can anyone please suggest a book (or a training class) that gets into
> the guts of complex queries? I need to understand, for example, the
> internal differences between this update query:
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> and this one:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> The second example ran for 20 minutes and I cancelled it; the first one
> finished in 2.5 minutes.
As Adam pointed out, the problem with the second query is that the derived
table is not correlated with the updated table, so you get a magnificent
cross join, and I'm not surprise that it did not complete in 20 minutes.
Since I don't know your tables, I cannot really a suggest a better way
to use the derived table. What you could write, is this:
Update PositionsEOM
Set MktVal = PositionsEOM.Quantity_ASD *
(Select PM.Price
FROM [Price Master] As PM
WHERE PositionsEOM.Fund_ID = PM.Fund_ID And
AND PositionsEOM.Position_Date = PM.Price_Date)
Here you don't have a derived table, but a correlated subquery. This is
actually the way you should do it, if you stick to ANSI. I need to
confess, though, that I prefer the syntax with FROM over the using
a correlated subquery.
> You wouldn't KNOW that you could write "Set" expressions like the
> first example, from ANY of the online documentation.
Nah, I would say that this is quite clear from the syntax diagram for
UPDATE in Books Online.
> Also, I decided that when you mix inner and outer joins in a select,
> which I do sometimes, if you change the order, the result set WILL be
> different (not just the time it takes). (I think this is true...)
Yes, this is correct. As long as you are only using inner joins, you
can have them in any order, but with left joins the table are turn.
Consider:
SELECT ...
FROM a
LEFT JOIN b ON a.col = b.col
JOIN c ON b.col2 = c.col2
What you probably want is:
SELECT
FROM a
LEFT JOIN (b JOIN c ON b.col2 = c.col2) ON a.col = b.col
In the first case, you take all rows in a, and if there is no matching
rows in b, you set all b:s colums to NULL. But then join the table
(a LEFT JOIN b) with c, and you will include rows that matches in c,
so all those NULL rows in b are now dropped.
In the second case you first join b with c, and then join this table
with a.
> I found one online article that mentioned that if you mix inner and
> outer joins in a query, you CAN control the order of the joins -- and it
> shows various ways to do that, including parentheses and derived or
> virtual tables. But only ONE online article. It's VERY rare to see any
> of this documented, and SQL Books Online sure doesn't show
> examples.......
It should be in the ANSI-92 standard for SQL, but it's hardly the kind
of book you read first.
The best book for reading about the various join techniques, is Joe
Celko's "SQL For Smarties", but of course it has the drawback that it
is not written for SQL Server. Tom Moreau's and Itzik Ben-Gan's book
is a better choice, if you want something which is more on target. And
quite some of these subjects are covered in Kalen Delaney's "Inside
SQL Server 2000". It was from her SQL7 book, that I learnt to use
paranthesis with JOIN.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns949B671D1EA5Yazorman@.127.0.0.1:
> As Adam pointed out, the problem with the second query is that the
> derived table is not correlated with the updated table, so you get a
> magnificent cross join, and I'm not surprise that it did not complete
> in 20 minutes.
Thanks to both of you for the answers. I will check out those books on
Amazon.
Regarding this join:
Update PositionsEOM
Set MktVal = Derived.MktVal From
(Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date) As Derived
Why is this a cross join? It has the phrase "Inner Join" in it (with
two conditions, hence the AND). I'm confused...
Thanks.
David Walker|||Hi David,
Thanks for you update.
First, I recommend the book I always use: <Inside SQL Server 2000> by Kalen
Delaney.
Second, let's take a little example, you could run in you Query Analyzer:
use pubs
go
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[authorsx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authorsx]
GO
CREATE TABLE [dbo].[authorsx] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
insert into authorsx select * from authors
Then
SET SHOWPLAN_TEXT ON
go
update authors
set authors.state=authorsx.state
from authors inner join authorsx
on authors.au_id=authorsx.au_id
go
update authors
set authors.state=xxx.state
from
(select authors.state
from authors inner join authorsx
on authors.au_id=authorsx.au_id) as xxx
--execution plan of first one ( similar to your first query)
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SET:([authors].[state]=[authorsx].[state]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([authorsx].[au_id]))
|--Table Scan(OBJECT:([pubs].[dbo].[authorsx]))
|--Clustered Index
Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SEEK:([authors].[au_id]=[authorsx].[au_id]) ORDERED FORWARD)
--execution plan of the second one (simular to your second query)
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SET:([authors].[state]=[authors].[state]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([authorsx].[au_id]))
| |--Table Scan(OBJECT:([pubs].[dbo].[authorsx]))
| |--Clustered Index
Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SEEK:([authors].[au_id]=[authorsx].[au_id]) ORDERED FORWARD)
|--Index
Scan(OBJECT:([pubs].[dbo].[authors].[test_index]), ORDERED FORWARD)
go
You will notice that there are tow nested loops in your second query but
there is only one in the first query.
So, in you last query, when update one row in your table authors, you have
to scan the table 'authorsx' one time. this is just like how the cross join
works:
From the SQL Server Books Online:
A cross join that does not have a WHERE clause produces the Cartesian
product of the tables involved in the join. The size of a Cartesian product
result set is the number of rows in the first table multiplied by the
number of rows in the second table. This is an example of a Transact-SQL
cross join:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The result set contains 184 rows (authors has 23 rows and publishers has 8;
23 multiplied by 8 equals 184).
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for the response. I'll have to study this to understand why, when I
say "Inner Join", I get a cross join. I believe you, I just have to figure
it out in order to understand it.
Thanks again to all for helping me with the detailed responses.
David Walker|||I think I can sum it up for you quite simply:
If you INNER JOIN but fail to provide a correlation (e.g. this thing in this
table = this thing in that table), the server has no way of knowing which
row in each table belongs with each row in the other table. So instead it
does the only option left: It pairs EVERY row of each table with EVERY row
of the other table.
Does that make sense?
"DW" <None> wrote in message news:uv8SpTI$DHA.1456@.TK2MSFTNGP09.phx.gbl...
> Thanks for the response. I'll have to study this to understand why, when
I
> say "Inner Join", I get a cross join. I believe you, I just have to
figure
> it out in order to understand it.
> Thanks again to all for helping me with the detailed responses.
> David Walker|||Could you guys, who have been so helpful, please explain how the cross join
comes about in the "derived" case?
It's clearly there, I just want to know how to align my thinking so that
it's obvious to me!!!
Thanks.
David Walker|||"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:eIwyUbI$DHA.392@.TK2MSFTNGP12.phx.gbl:
> I think I can sum it up for you quite simply:
> If you INNER JOIN but fail to provide a correlation (e.g. this thing
> in this table = this thing in that table), the server has no way of
> knowing which row in each table belongs with each row in the other
> table. So instead it does the only option left: It pairs EVERY row
> of each table with EVERY row of the other table.
> Does that make sense?
>
Yes, that helps. It's becoming clearer as I read these responses.
So where I have:
Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
...doesn't "correlate" the Set statement?
David Walker|||"DW" <None> wrote in message news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
> ...doesn't "correlate" the Set statement?
No; roughly translated to English, that says ONLY: "Set the value of
tblA.field to the value of derivedtbl.field."
What you want it to say is: "Set the value of tblA.field to the value of
derivedtbl.field when the row to update in tblA is identified as a row that
corresponds to a row in derivedtbl." (or even more precisely, "Update the
value of tblA.field only when it is identified as a corresponding row to one
in derivedtbl, and use only the value from derivedtbl.field from that
corresponding row to update tblA.field.")
In other words, the first statement gives the database no indication
about how or in what cases to update tblA.field; so it does so for every row
of tblA, and it does so once for every value it sees in derivedtbl. It has
no other choice. What you want to do instead is something like:
UPDATE tblA
SET tblA.field = derivedtbl.field
FROM TblA
INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id =derivedtbl.id
In this case, you're saying that for every row in derivedtbl, the server
should look for the row (or those rows) in tblA where the value of the
column "id" is the same as the value of the column "id" in derivedtbl. So
now the server knows which rows match and can therefore update the correct
rows, only once, rather than every row, for every possible value.|||AH! That helps. I could use a derived table, but I still need to join
that derived table with the original table.
The first example that doesn't use a derived table works fine, but I
certainly want to understand the differences in the approaches.
Some better tools than Enterprise Manager would also be nice ;-) where
I could say what you just said, and have it implemented.
Thanks to all of you for the help.
David Walker
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:OI7KL$I$DHA.2512@.TK2MSFTNGP11.phx.gbl:
> "DW" <None> wrote in message
> news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
>> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
>> ...doesn't "correlate" the Set statement?
> No; roughly translated to English, that says ONLY: "Set the value
> of
> tblA.field to the value of derivedtbl.field."
> What you want it to say is: "Set the value of tblA.field to the
> value of
> derivedtbl.field when the row to update in tblA is identified as a row
> that corresponds to a row in derivedtbl." (or even more precisely,
> "Update the value of tblA.field only when it is identified as a
> corresponding row to one in derivedtbl, and use only the value from
> derivedtbl.field from that corresponding row to update tblA.field.")
> In other words, the first statement gives the database no
> indication
> about how or in what cases to update tblA.field; so it does so for
> every row of tblA, and it does so once for every value it sees in
> derivedtbl. It has no other choice. What you want to do instead is
> something like:
> UPDATE tblA
> SET tblA.field = derivedtbl.field
> FROM TblA
> INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id => derivedtbl.id
> In this case, you're saying that for every row in derivedtbl, the
> server should look for the row (or those rows) in tblA where the value
> of the column "id" is the same as the value of the column "id" in
> derivedtbl. So now the server knows which rows match and can
> therefore update the correct rows, only once, rather than every row,
> for every possible value.
>
>|||P.S. The Update query (the fast one that works):
Update PositionsEOM
Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
From PositionsEOM Inner Join [Price Master] As PM On
PositionsEOM.Fund_ID = PM.Fund_ID And
PositionsEOM.Position_Date = PM.Price_Date
doesn't clearly say that the rows should "correspond", does it?
David Walker
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:OI7KL$I$DHA.2512@.TK2MSFTNGP11.phx.gbl:
> "DW" <None> wrote in message
> news:#7CLN2I$DHA.2072@.TK2MSFTNGP11.phx.gbl...
>> Set tblA.field = derivedtbl.field from (inner join) as derivedtbl
>> ...doesn't "correlate" the Set statement?
> No; roughly translated to English, that says ONLY: "Set the value
> of
> tblA.field to the value of derivedtbl.field."
> What you want it to say is: "Set the value of tblA.field to the
> value of
> derivedtbl.field when the row to update in tblA is identified as a row
> that corresponds to a row in derivedtbl." (or even more precisely,
> "Update the value of tblA.field only when it is identified as a
> corresponding row to one in derivedtbl, and use only the value from
> derivedtbl.field from that corresponding row to update tblA.field.")
> In other words, the first statement gives the database no
> indication
> about how or in what cases to update tblA.field; so it does so for
> every row of tblA, and it does so once for every value it sees in
> derivedtbl. It has no other choice. What you want to do instead is
> something like:
> UPDATE tblA
> SET tblA.field = derivedtbl.field
> FROM TblA
> INNER JOIN (SELECT field, id FROM ...) derivedtbl ON TblA.id => derivedtbl.id
> In this case, you're saying that for every row in derivedtbl, the
> server should look for the row (or those rows) in tblA where the value
> of the column "id" is the same as the value of the column "id" in
> derivedtbl. So now the server knows which rows match and can
> therefore update the correct rows, only once, rather than every row,
> for every possible value.
>|||"DW" <None> wrote in message news:#CwLzmL$DHA.2800@.tk2msftngp13.phx.gbl...
> P.S. The Update query (the fast one that works):
> Update PositionsEOM
> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date
> doesn't clearly say that the rows should "correspond", does it?
Sure it does...
General UPDATE syntax:
UPDATE X
...
FROM X
...
X is the same table in both places... So in your query, you're actually
saying, "First join PositionsEOM and [Price Master] where the Fund_ID
columns are equal to each other and the Position_Date is equal to the
Price_Date. Then, update those rows in PositionsEOM that satisfied the
join."
As for better tools than Enterprise Manager, maybe you should give Query
Analyzer a try.|||Yes, I use QA also. I want to be able to drag and drop two tables, link
the fields with an inner join, then put huge parentheses around those
two tables and the iner join, then drag another table in there and make
an outer join. Can any tools do that?
Thanks.
David
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
news:#ZBeOqL$DHA.2800@.tk2msftngp13.phx.gbl:
> "DW" <None> wrote in message
> news:#CwLzmL$DHA.2800@.tk2msftngp13.phx.gbl...
>> P.S. The Update query (the fast one that works):
>> Update PositionsEOM
>> Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
>> From PositionsEOM Inner Join [Price Master] As PM On
>> PositionsEOM.Fund_ID = PM.Fund_ID And
>> PositionsEOM.Position_Date = PM.Price_Date
>> doesn't clearly say that the rows should "correspond", does it?
> Sure it does...
> General UPDATE syntax:
> UPDATE X
> ...
> FROM X
> ...
> X is the same table in both places... So in your query, you're
> actually
> saying, "First join PositionsEOM and [Price Master] where the Fund_ID
> columns are equal to each other and the Position_Date is equal to the
> Price_Date. Then, update those rows in PositionsEOM that satisfied
> the join."
> As for better tools than Enterprise Manager, maybe you should give
> Query
> Analyzer a try.
>
>|||DW (None) writes:
> Regarding this join:
> Update PositionsEOM
> Set MktVal = Derived.MktVal From
> (Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
> From PositionsEOM Inner Join [Price Master] As PM On
> PositionsEOM.Fund_ID = PM.Fund_ID And
> PositionsEOM.Position_Date = PM.Price_Date) As Derived
> Why is this a cross join? It has the phrase "Inner Join" in it (with
> two conditions, hence the AND). I'm confused...
The derived table has an inner join, but you have hidden cross join
between PositionsEOM and the derived table. (The fact that PositionsEOM
appears in the derived table is irrelevant.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi David,
Thanks for using your update.
Well, as far as I know, on SQL Server side ,there is no such an amazing
tool for us to use.
For design database, Microsoft Viso 2000 could be a good tool, but I could
not remember any tool that could create queries like that.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||"DW" <None> wrote in message news:eDZuU4L$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Yes, I use QA also. I want to be able to drag and drop two tables, link
> the fields with an inner join, then put huge parentheses around those
> two tables and the iner join, then drag another table in there and make
> an outer join. Can any tools do that?
I know this is going to sound strange... But you actually DON'T want to
do that. If you were to use a graphical tool like that, you would never
learn the reasons why, for instance, your first query and second query
weren't doing the same thing. Because you'd never see them, just some
pretty pictures on the screen. Maybe someday there will be a tool like
you're talking about (there's not right now, as far as I know), but when
that day comes you will have been typing this stuff for so long that you'll
no longer want it.|||Is this example:
USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
GO
... a correlated subquery? In the subquery, you don't specify the
authors table, and the subquery won't work standing alone. Is there a
join in here?
Thanks.
David Walker|||Since everyone here has been so helpful, is there a way to do this:
Update PositionsEOM
Set Sec_Type = AM.Sec_Type
From PositionsEOM Inner Join [Asset Master] As AM On
PositionsEOM.Fund_ID = AM.Fund_ID
Without a Join?
Thanks.
David Walker|||You could, but you're probably better off with a JOIN... but here's how
anyway:
Update PositionsEOM
Set Sec_Type = (SELECT AM.Sec_Type FROM [Asset Master] As AM WHERE
PositionsEOM.Fund_ID = AM.Fund_ID)
This assumes that there is exactly one row in [Asset Master] for each row in
PositionsEOM. If there are less rows, you will end up with NULL Sec_Type,
and if there are more you will get the following error: "Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <,
<= , >, >= or when the subquery is used as an expression."
So, you're much better off with a JOIN.
"DW" <None> wrote in message news:eszH8uW$DHA.2308@.tk2msftngp13.phx.gbl...
> Since everyone here has been so helpful, is there a way to do this:
> Update PositionsEOM
> Set Sec_Type = AM.Sec_Type
> From PositionsEOM Inner Join [Asset Master] As AM On
> PositionsEOM.Fund_ID = AM.Fund_ID
> Without a Join?
> Thanks.
> David Walker|||The Fund_ID is the primary key, and dups are not allowed. But I'll
leave the Join. There are some missing assets, so I occasionally get
nulls for the security type. (We get data from an outside source, and
there are some referential integrity problems. We can't enforce
referential integrity; I wish we could, but we need to accept
transactions even if the asset doesn't exist, and so on.)
So the subquery is an "expression" in your example, and it magically
knows which rows of PositionsEOM go with which rows of Asset Master?
I have written SQL statements and views, mostly with Enterprise Manager,
but now I want to understand them 100%.
Thanks so much for the help. I ordered two books that were recommended,
so when I'm done with them, all of this should be crystal clear!
David Walker|||"DW" <None> wrote in message news:u3JrcDY$DHA.3536@.tk2msftngp13.phx.gbl...
> So the subquery is an "expression" in your example, and it magically
> knows which rows of PositionsEOM go with which rows of Asset Master?
The subquery in the example is referred to as a "correlated subquery"
because the inner query correlates to the outer query... Nothing magical
about it ;) It looks for tables referenced in the outer query and if
they're referenced in the inner query then their used (and therefore
"correlated").
As for the RI, at least in the JOIN we've been considering here you have
no reason not to be able to enforce a foreign key from [Asset Master]
pointing to the Fund_ID primary key on PositionsEOM...
Anyway, good luck w/ those books, I'm sure you'll enjoy them!|||Adam Machanic (amachanic@.air-worldwide.nospamallowed.com) writes:
> This assumes that there is exactly one row in [Asset Master] for each
> row in PositionsEOM. If there are less rows, you will end up with NULL
> Sec_Type, and if there are more you will get the following error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <,
><= , >, >= or when the subquery is used as an expression."
> So, you're much better off with a JOIN.
Hm, that is more the argument for the subquery.
As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
is an extention which is peculiar to Microsoft, Sybase and I think Informix.
The advocates of the subquery thinks the JOIN is bad, because if your
join conditions are too few, you will update the same row many times,
but not notice, execpt that performance is bad.
Myself, I've used the JOIN syntax for many years, and I prefer it. Since
it is the same syntax as for SELECT, it's the same concept to understand.
Also, it's easier to test what rows you match, by rewriting the UPDATE into
a SELECT.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949D2D61EA6BYazorman@.127.0.0.1...
> As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
> is an extention which is peculiar to Microsoft, Sybase and I think
Informix.
Not sure what you mean here. AFAIK, the SQL-92 standard has JOIN as
part of the SELECT syntax.|||Adam Machanic (amachanic@.air-worldwide.nospamallowed.com) writes:
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns949D2D61EA6BYazorman@.127.0.0.1...
>> As I think I said earlier, ANSI SQL does not have the JOIN syntax. That
>> is an extention which is peculiar to Microsoft, Sybase and I think
> Informix.
> Not sure what you mean here. AFAIK, the SQL-92 standard has JOIN as
> part of the SELECT syntax.
Yes, but this applied to UPDATE (and DELETE) where ANSI SQL does not
have FROM (and thus not JOIN).
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp