Query your Stored Procs

Is there a way you can query stored procedures for a string in all of the
stored procs in a database? For example, if you were looking for "LETTERS"
in all of your stored procedures in your Account DB, how would you look for
the string? Please post example if you know.
-Toco-You can search the syscomments which has the source code for all your stored
procedures. A wrapper script can be found at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
Anith|||SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM AccountDB.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%LETTERS%'
http://www.aspfaq.com/
(Reverse address to reply.)
"Toco" <Toco@.discussions.microsoft.com> wrote in message
news:5A3A9543-40A4-460D-BFDC-09C021991BDD@.microsoft.com...
> Is there a way you can query stored procedures for a string in all of the
> stored procs in a database? For example, if you were looking for
"LETTERS"
> in all of your stored procedures in your Account DB, how would you look
for
> the string? Please post example if you know.
> -Toco-

Query works in 2000 but not 2005

I noticed that some queries work in 2000, but not in 2005, for example
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD)
Works for 2K, but for 2K5 i get an error message saying "columnD is
invalid in the select list because it is not contained...etc etc"
ANyone know of the change?It shouldn't work on SQL2000 either. Your CASE expression is not complete and
there should be a comma before sum(columnE).
The following should work on both versions:
SELECT columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end,
sum(columnE)
FROM TableA
GROUP BY
columnA,
columnB,
case when columnC = 'BLAH' then dbo.fn_func(columnD) end
Linchi
"adauti@.gmail.com" wrote:
> I noticed that some queries work in 2000, but not in 2005, for example
>
> SELECT columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> sum(columnE)
> FROM TableA
> GROUP BY
> columnA,
> columnB,
> case when columnC = 'BLAH' then dbo.fn_func(columnD)
> Works for 2K, but for 2K5 i get an error message saying "columnD is
> invalid in the select list because it is not contained...etc etc"
> ANyone know of the change?
>

Query with union no longer updatable in SQL 2005

I have a query that uses a union that was updatable in SQL 2000 but not SQL
2005
Here is a simple example of a result set that is updatable in SQL 2000 but
not SQL 2005
Northwind DB
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ALFKI')
UNION ALL
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ANATR')Richard
Can you show us your UPDATE statement?
"Richard Brown" <RichardBrown@.discussions.microsoft.com> wrote in message
news:C5D146D9-CA8E-4984-8417-08BEFCFE3816@.microsoft.com...
>I have a query that uses a union that was updatable in SQL 2000 but not SQL
> 2005
> Here is a simple example of a result set that is updatable in SQL 2000 but
> not SQL 2005
> Northwind DB
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ALFKI')
>
> UNION ALL
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ANATR')|||The problem was discussed in MSDN Product Feedback Center in the
following bug report (which was closed with the resolution "by
design"):
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
Razvan

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

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/techin.../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 N
ULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[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 F
ORWARD)
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.

Query to update 1 record in a duplicate set of records

How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

Without more info it's hard to tell, but you just need to qualify what you want to update:

UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'

AND orderid = 'Whatever'
GO

|||Thanks for your response but I can't specify an orderid because there are 3612 records and within those 3612 records some of the id's are dups. The query above would do a specific orderid and a specific date. The dates are all different I just want the most recent per orderid. Thank you anyway.|||

Something along the lines of:

UPDATE Table1

SET NewCol = 1

FROM Table1 AS i

INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate

FROM Table1

GROUP BY OrderID

HAVING COUNT(*) > 1) AS Dupes

ON i.OrderID = Dupes.OrderID

AND i.OrderDate = Dupes.OrderDate

|||Thank you that is close enough to what I was looking for. I appreciate your response

Query to select data based on alphanumeric (surname) information.

Hello,

I am trying to write a query that will be able to select different segments of data based on spelling of the last name.

For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'

then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...

I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?

Thanks

It isn't perfectly straightforward, particularly at the end of the ranges, but this will work...

create table person

(

lastName varchar(20)

)

insert into person

select 'AAA Dude'

union all

select 'Branson'

union all

select 'EJJ Dude'

union all

select 'Flighter'

union all

select 'Jaenor'

union all

select 'Zoinks'

--first

select *

from person

where lastName >= 'a' --Just one letter needed here because we want all

and lastName < 'EJK' --Added one letter to the range you want

lastName

--

AAA Dude

Branson

EJJ Dude

select *

from person

where lastName >= 'EJK'

and lastName < 'JAF' --again, one character more than the range

lastName

--

Flighter

Jaenor

select *

from person

where lastName >= 'JAF'

and lastName <= replicate('Z',20) --All zzz's would be the end of the range

--replicated to the max length of the column

lastName

--

Zoinks

|||

The following querry may fit for you,

Code Snippet

Create Table #person (

[lastName] Varchar(100)

);

Insert Into #person Values('AAA Dude');

Insert Into #person Values('Branson');

Insert Into #person Values('EJJ Dude');

Insert Into #person Values('Flighter');

Insert Into #person Values('Jaenor');

Insert Into #person Values('Zoinks');

Insert Into #person Values('EJK');

Insert Into #person Values('EJ');

Insert Into #person Values('EJJ');

Insert Into #person Values('EJJ ZZZZZZ');

Insert Into #person Values('JA');

Insert Into #person Values('JAE');

Select * from #person

Where

[lastName] >= 'AAA'

And [lastName] <= 'EJJ' + Replicate(Char(255),100) -- change the length with your datatype

Select * from #person

Where

[lastName] >= 'EJK'

And [lastName] <= 'JAE' + Replicate(Char(255),100) -- change the length with your datatype

|||

The reason I went with:

where lastName >= 'a' --Just one letter needed here because we want all

Was that I wanted to make sure that all alpha numeric values got in, even if the value 'A boinger' was in the table. If there are no spaces in the code value, it wouldn't matter. For example:

select case when 'a boinger' >= 'AAA' then 'yes' else 'no' end

Would be 'no' since 'a b' < 'AAA'

|||

Having worked with large criminal justice applications/databases, I've noticed that often when arrested, folks may have 'odd' street names, or the arresting officer is dealing with someone unconscious or intoxicated, so the name in the database may be '10 minute Fred', '2 Drink Limit', '1perp', '2ndDrunk', etc.

So, I would not limit names to starting with alpha...

('10 minute Fred' was never in a house/business for more than 10 minutes when he was burglurizing them.)

Query to return Xml column data as relational table - how?

Greetings,

I've just begun storing Xml in a SQL Server Xml column. I've got a column that contains something like this 3 element example:

<document xmlns="http://www.lotus.com/dxl" version="7.0" maintenanceversion="2.0" replicaid="852571B800111CE3" form="data">

<item name="OriginalModTime">

<datetime dst="true">20060813T135156,51-05</datetime>

</item>

<item name="Genius_Status_1">

<text>1</text>

</item>

<item name="archive_date">

<datetime>20040331</datetime>

</item>

</document>

I need to write a query that will return a combination of attribute and data values at different "levels". This is what I must achieve as query results:

Name Type Value

- - --

OriginalModTime datetime 20060813T135156,51-05

Genius_Status_1 text 1

archive_date datetime 20040331

Each resultset row must correspond to one "item" element.

Can someone give me an idea of what this query should look like? I'm trying to puzzle my way through xquery...

Thanks,

BCB

I've got everything but the "Type" column figured out. This query returns "Name" and "Value". Can someone suggest the missing logic to return the "Type" value?

Thanks... BCB

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') as [Notes Field],

Item.value('.', 'NVARCHAR(MAX)') as Value

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

|||

This is the working query:

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') AS [Notes Field Name],

Item.value('local-name(./*[1])', 'NVARCHAR(256)') AS [Data Type],

Item.value('.', 'NVARCHAR(MAX)') AS [Field Value]

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

sql

Query to return Xml column data as relational table - how?

Greetings,

I've just begun storing Xml in a SQL Server Xml column. I've got a column that contains something like this 3 element example:

<document xmlns="http://www.lotus.com/dxl" version="7.0" maintenanceversion="2.0" replicaid="852571B800111CE3" form="data">

<item name="OriginalModTime">

<datetime dst="true">20060813T135156,51-05</datetime>

</item>

<item name="Genius_Status_1">

<text>1</text>

</item>

<item name="archive_date">

<datetime>20040331</datetime>

</item>

</document>

I need to write a query that will return a combination of attribute and data values at different "levels". This is what I must achieve as query results:

Name Type Value

- - --

OriginalModTime datetime 20060813T135156,51-05

Genius_Status_1 text 1

archive_date datetime 20040331

Each resultset row must correspond to one "item" element.

Can someone give me an idea of what this query should look like? I'm trying to puzzle my way through xquery...

Thanks,

BCB

I've got everything but the "Type" column figured out. This query returns "Name" and "Value". Can someone suggest the missing logic to return the "Type" value?

Thanks... BCB

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') as [Notes Field],

Item.value('.', 'NVARCHAR(MAX)') as Value

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

|||

This is the working query:

SELECT TOP 1000

Item.value('./@.name', 'NVARCHAR(MAX)') AS [Notes Field Name],

Item.value('local-name(./*[1])', 'NVARCHAR(256)') AS [Data Type],

Item.value('.', 'NVARCHAR(MAX)') AS [Field Value]

FROM

NotesAudit

CROSS APPLY

XmlBlob.nodes('declare namespace MI="http://www.lotus.com/dxl"; /MIocument/MI:item') AS T1(Item)

query to retrieve the columns that are null in a table

Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advanceHi Teixeira,
I'm not sure what you are asking. If you could supply a table creation script some test data, and what the "result" should be based on the test data, that would help enormously.

Thanks,
Cat|||USE [myDB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL

this is my struture adapted.
based on this, i want to know which columns are not NULL, for my qyery result do not display for example 10 Quantity columns when i have just 3 that have quantities.|||Do you expect your query to return a single rowset, or is it possible to return multiple rows?|||yes!
It can return several rows.
but its not necessary to return columns that has null or empty values, because it would generated a lot of unnecessary columns in my datagrid display object|||I would change your structure from this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[quantity1] [decimal](18, 2) NOT NULL,
[quantity2] [decimal](18, 2) NULL,
[quantity3] [decimal](18, 2) NULL,
[quantity4] [decimal](18, 2) NULL,
[quantity5] [decimal](18, 2) NULL,
[quantity6] [decimal](18, 2) NULL,
[quantity7] [decimal](18, 2) NULL,
[quantity8] [decimal](18, 2) NULL,
[quantity9] [decimal](18, 2) NULL,
[quantity10] [decimal](18, 2) NULL)

to this:

CREATE TABLE [dbo].[books](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[book_description] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL)
GO
CREATE TABLE [dbo].[bookquantity](
[book_id] [int] IDENTITY(1,1) NOT NULL,
[quantity] [decimal](18, 2) NOT NULL)
GO
ALTER TABLE [dbo].[bookquantity]
ADD CONSTRAINT FK_book (book_id) REFERENCE [books] (book_id)
GO

This way you are not tied to only 10 quantities and you can don't need to even store the NULL values.

If you can't change the structure of the table, I would suggest either creating a temp table with the above structure and populating it with the data from the master table so that you can weed out the nulls, or creating a single delimited string which the application can parse through. SQL can't really handle returning a result set with a variable number of fields.

The first suggestion would yield a result set like:
book_id quantity
------
1 12.70
1 33.45
1 9.00

The second suggestion would yield a result set like:
book_id quantity_list
--------
1 12.70|33.45|9.00

Hope this helps.
Cat|||I think you're both ideas are a good solution.
As i've some data already in the tables, normalize it more as you suggested would'd take me more time, but the second idea solves the problem perfectly.

Thanks for the help.

Teixeira

Query to filter duplicate data from table.

i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.Originally posted by pln_verma
i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

check this :
http://www.sqlteam.com/item.asp?ItemID=3331|||Does your table have a surrogate key or any method of uniquely identifying a record?

query synonyms

I'm using a full text index to search organization names. My language word
breaker is French (France).
My situation is that if I search for example for a organization with the word
"meilleur" in the name ("meilleur" means "greatest" in english), the answer
that the search will give me (using freetext or formsof(inflectional))is all
the organizations with "meilleur", "bon" and "bonne" in there name. But the
thing is that my client don't want the organizations with "bon" and "bonne"
(who means "good" in english) for there answer.
I made a little search and the answer that I found is that these are synonyms
and they should be listed in my tsfra.xml file. This file looks like this :
- <XML ID="Tahoe Thesaurus">
- <!--
Commented out
<thesaurus xmlns="x-schema:tsSchema.xml">
<expansion>
<sub weight="0.8">Internet Explorer</sub>
<sub weight="0.2">IE</sub>
<sub weight="0.9">IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub weight="1.0">Windows 2000</sub>
</replacement>
<expansion>
<sub weight="0.5">run**</sub>
<sub weight="0.5">jog**</sub>
</expansion>
</thesaurus>
-->
</XML>
there's nothing that indicate that meilleur is synonyms of good.
Know I wonder is there another files somewhere who can have these synonyms?
Could it be in the Microsoft search engine?
Thanks in advance,
Nik
P.S. Sorry for my english
Message posted via http://www.droptable.com
This is hard coded in the stemmer. Unfortunately there is nothing you can do
other than to write your own stemmer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nick via droptable.com" <forum@.droptable.com> wrote in message
news:52A31264BED2E@.droptable.com...
> I'm using a full text index to search organization names. My language
> word
> breaker is French (France).
> My situation is that if I search for example for a organization with the
> word
> "meilleur" in the name ("meilleur" means "greatest" in english), the
> answer
> that the search will give me (using freetext or formsof(inflectional))is
> all
> the organizations with "meilleur", "bon" and "bonne" in there name. But
> the
> thing is that my client don't want the organizations with "bon" and
> "bonne"
> (who means "good" in english) for there answer.
> I made a little search and the answer that I found is that these are
> synonyms
> and they should be listed in my tsfra.xml file. This file looks like this
> :
> - <XML ID="Tahoe Thesaurus">
> - <!--
> Commented out
> <thesaurus xmlns="x-schema:tsSchema.xml">
> <expansion>
> <sub weight="0.8">Internet Explorer</sub>
> <sub weight="0.2">IE</sub>
> <sub weight="0.9">IE5</sub>
> </expansion>
> <replacement>
> <pat>NT5</pat>
> <pat>W2K</pat>
> <sub weight="1.0">Windows 2000</sub>
> </replacement>
> <expansion>
> <sub weight="0.5">run**</sub>
> <sub weight="0.5">jog**</sub>
> </expansion>
> </thesaurus>
>
> -->
> </XML>
>
> there's nothing that indicate that meilleur is synonyms of good.
> Know I wonder is there another files somewhere who can have these
> synonyms?
> Could it be in the Microsoft search engine?
> Thanks in advance,
> Nik
> P.S. Sorry for my english
>
> --
> Message posted via http://www.droptable.com