SEO

This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)


With this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.


But this statement:
select * from ded_temp a
where a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
Runs without error:
Why? and How should I change the first statement to run my update. This


statement of course works fine in Oracle.
tks
ken.

Hi
Just try it out this way:

update ded_temp
set balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)



best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

kalamos (kmcclare@hotmail.com) writes:
> This statement fails
> update ded_temp a
> set a.balance = (select sum(b.ln_amt)
> from ded_temp b
> where a.cust_no = b.cust_no
> and a.ded_type_cd = b.ded_type_cd
> and a.chk_no = b.chk_no
> group by cust_no, ded_type_cd, chk_no)

update ded_temp
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
from ded_temp a

None of the syntaxes are in line with standard SQL, so different enginge
have added different place where you can put in the alias.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On Fri, 3 Jun 2005 00:06:03 -0400, kalamos wrote:

>This statement fails
>update ded_temp a
> set a.balance = (select sum(b.ln_amt)
> from ded_temp b
> where a.cust_no = b.cust_no
> and a.ded_type_cd = b.ded_type_cd
> and a.chk_no = b.chk_no
> group by cust_no, ded_type_cd, chk_no)
>
>
>With this error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'a'.
(snip)

Hi ken,

Erland already pointed out that the proprietary UPDATE FROM syntax
differs between products. However, why use proprietary code when you can
use ANSI-standard code that will work on almost all databases:

update ded_temp
set balance = (select sum(b.ln_amt)
from ded_temp b
where ded_temp.cust_no = b.cust_no
and ded_temp.ded_type_cd = b.ded_type_cd
and ded_temp.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)

BTW, you can also omit the GROUP BY clause, since the subquery will only
match rows for one set of (cust_no, ded_type_cd, chk_no) anyway - this
might even give you some performance gain!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> Erland already pointed out that the proprietary UPDATE FROM syntax
> differs between products. However, why use proprietary code when you can
> use ANSI-standard code that will work on almost all databases:
>
> update ded_temp
> set balance = (select sum(b.ln_amt)
> from ded_temp b
> where ded_temp.cust_no = b.cust_no
> and ded_temp.ded_type_cd = b.ded_type_cd
> and ded_temp.chk_no = b.chk_no
> group by cust_no, ded_type_cd, chk_no)

Could you please explain what this miserable piece of code means? You
have two ded_temp in the query, so which ded_temp does ded_temp.cust_no
refer to?

You will have to excuse, but I think it's poor advice to suggest that
people should use code that is ambiguous. I don't really care if there
is wording in ANSI that disamguiates the query, it's still bad practice,
because for a human the query is about incomprehensible. (And I would
not be surprised if more than one engine gets lost on the query above,
so I would not even trust the elusive compatibility.)

The ANSI standard does not provide a place to put in an alias, and
that is a serious shortcoming. In this case, it leads to the nonsese
above. In other cases, imagine that you have a table by the of
instrumentclearingmarketplaces - writing that over and over again
is completely out of the question.

> BTW, you can also omit the GROUP BY clause, since the subquery will only
> match rows for one set of (cust_no, ded_type_cd, chk_no) anyway - this
> might even give you some performance gain!

For some real serious peformance gain, this is likely to be a true
winner:

UPDATE ded_temp
SET balance = b.ln_amt
FROM ded_temp a
JOIN (SELECT cust_no, ded_type_cd, chk_no, ln_amt = sum(b.ln_amt)
FROM ded_temp
GROUP BY cust_no, ded_type_cd, chk_no) AS b
ON a.cust_no = b.cust_no
AND a.ded_type_cd = b.ded_type_cd
AND a.ded_temp.chk_no = b.chk_no

I can give no guarantees, but my experience is that a join with a derived
table results in a lot more effecient plan, than a correlated subquery.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

The above solutions were great. I only had the "a" in the wrong place. I did
not understand that sql server was "different" from Oracle in this way.

Thanks for all your help.

I'm not really sure what the code means, I'm not the programmer just the
administrator, I got this code second hand.

thanks again,
ken.


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns966B6BBFE57FYazorman@127.0.0.1...
> Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
>> Erland already pointed out that the proprietary UPDATE FROM syntax
>> differs between products. However, why use proprietary code when you can
>> use ANSI-standard code that will work on almost all databases:
>>
>> update ded_temp
>> set balance = (select sum(b.ln_amt)
>> from ded_temp b
>> where ded_temp.cust_no = b.cust_no
>> and ded_temp.ded_type_cd = b.ded_type_cd
>> and ded_temp.chk_no = b.chk_no
>> group by cust_no, ded_type_cd, chk_no)
>
> Could you please explain what this miserable piece of code means? You
> have two ded_temp in the query, so which ded_temp does ded_temp.cust_no
> refer to?
>
> You will have to excuse, but I think it's poor advice to suggest that
> people should use code that is ambiguous. I don't really care if there
> is wording in ANSI that disamguiates the query, it's still bad practice,
> because for a human the query is about incomprehensible. (And I would
> not be surprised if more than one engine gets lost on the query above,
> so I would not even trust the elusive compatibility.)
>
> The ANSI standard does not provide a place to put in an alias, and
> that is a serious shortcoming. In this case, it leads to the nonsese
> above. In other cases, imagine that you have a table by the of
> instrumentclearingmarketplaces - writing that over and over again
> is completely out of the question.
>
>> BTW, you can also omit the GROUP BY clause, since the subquery will only
>> match rows for one set of (cust_no, ded_type_cd, chk_no) anyway - this
>> might even give you some performance gain!
>
> For some real serious peformance gain, this is likely to be a true
> winner:
>
> UPDATE ded_temp
> SET balance = b.ln_amt
> FROM ded_temp a
> JOIN (SELECT cust_no, ded_type_cd, chk_no, ln_amt = sum(b.ln_amt)
> FROM ded_temp
> GROUP BY cust_no, ded_type_cd, chk_no) AS b
> ON a.cust_no = b.cust_no
> AND a.ded_type_cd = b.ded_type_cd
> AND a.ded_temp.chk_no = b.chk_no
>
> I can give no guarantees, but my experience is that a join with a derived
> table results in a lot more effecient plan, than a correlated subquery.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

This is exactly the answer I was looking for, so simple yet so difficult to
find.

thanks for your help



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns966A612BFC362Yazorman@127.0.0.1...
> kalamos (kmcclare@hotmail.com) writes:
>> This statement fails
>> update ded_temp a
>> set a.balance = (select sum(b.ln_amt)
>> from ded_temp b
>> where a.cust_no = b.cust_no
>> and a.ded_type_cd = b.ded_type_cd
>> and a.chk_no = b.chk_no
>> group by cust_no, ded_type_cd, chk_no)
>
> update ded_temp
> set a.balance = (select sum(b.ln_amt)
> from ded_temp b
> where a.cust_no = b.cust_no
> and a.ded_type_cd = b.ded_type_cd
> and a.chk_no = b.chk_no
> group by cust_no, ded_type_cd, chk_no)
> from ded_temp a
>
> None of the syntaxes are in line with standard SQL, so different enginge
> have added different place where you can put in the alias.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

>> The ANSI standard does not provide a place to put in an alias, and that is a serious shortcoming. <<

The reason for that is a consistent model of alias in Standard SQL. A
correlation names acts as if it makes a new working table, whcih wil
disappear at the end of the statement. You would never update the base
table if you allowed an alias in the UPDATE statement.

kalamos (kmcclare@hotmail.com) writes:
> The above solutions were great. I only had the "a" in the wrong place. I
> did not understand that sql server was "different" from Oracle in this
> way.

Just a word of warning, while most, if nor all, RDBMSs today provides
SQL, that does not mean that SQL from one engine runs on another. You
cannot even rely on some sort of standard, because few engines implement
all of the standard.

....and even if the statement runs and give the same result on two
engines, performance may differ considerably.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

--CELKO-- wrote:
>>>The ANSI standard does not provide a place to put in an alias, and that is a serious shortcoming. <<
>
>
> The reason for that is a consistent model of alias in Standard SQL. A
> correlation names acts as if it makes a new working table, whcih wil
> disappear at the end of the statement. You would never update the base
> table if you allowed an alias in the UPDATE statement.
>
Are you sure about that? I thought the alias is just the "exposed name".
Just like CREATE ALIAS/SYNONYM does not make a copy of the table
labeling a table with a different exposed name does not (semantically)
make a copy.
What makes the copy is the SELECT (or UNION , ...) because it produces a
new "derived table". Whether it has a name or not conceptually has no
effect on its existance.
Interestingly this all falls apart on UPDATE/DELETE/INSERT target when
one looks at updatable views which clearly are derived tables but do
what one can reasonably expect: modify the "underlying" table.
FWIW some other DBMS support aliasing of UPDATE and DELETE targets.
(Makes obviously no sense on INSERT)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab