473,854 Members | 1,534 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT works but UPDATE fails. ?

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.

Jul 23 '05 #1
17 5037
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 ***
Jul 23 '05 #2
kalamos (km******@hotma il.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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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_n o = b.cust_no
and ded_temp.ded_ty pe_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)
Jul 23 '05 #4
Hugo Kornelis (hugo@pe_NO_rFa ct.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_n o = b.cust_no
and ded_temp.ded_ty pe_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_n o
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 incomprehensibl e. (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
instrumentclear ingmarketplaces - 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
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" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Hugo Kornelis (hugo@pe_NO_rFa ct.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_n o = b.cust_no
and ded_temp.ded_ty pe_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_n o
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 incomprehensibl e. (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
instrumentclear ingmarketplaces - 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, es****@sommarsk og.se

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

Jul 23 '05 #6
This is exactly the answer I was looking for, so simple yet so difficult to
find.

thanks for your help
:)
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
kalamos (km******@hotma il.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, es****@sommarsk og.se

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

Jul 23 '05 #7
>> 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.

Jul 23 '05 #8
kalamos (km******@hotma il.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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
--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
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9637
by: Arthur | last post by:
Hey Everyone, Is this an Oracle bug? Here is my cursor statement: CURSOR tax_portal_cursor IS SELECT * FROM web_payment@remotedb WHERE caps_code IN ( SELECT * FROM TABLE(CAST(l_caps_codes AS myTableType)) ) AND processed_datetime IS NULL
4
3263
by: Paul | last post by:
I have run into a strange problem with a site I am working on. My SELECT queries work fine, but I just tried to add an UPDATE statement and it fails. A test showed that INSERT fails also. I created a new table just for testing called "blah" and it had the same results. My statements: "UPDATE GiftCerts SET Paid=1 WHERE CertSerial='2005-0001'" and "INSERT INTO blah (test) VALUES ('something')"
29
3200
by: Mainlander | last post by:
An ISP I belong to uses Majordomo for their mailing list system. I'd like to encourage them to move to a system that uses a database, preferably psql which they already run on their server. Anything out there in Php?
3
14580
by: William Wisnieski | last post by:
Hello Again, I'm really stuck on this one.....so I'm going to try a different approach to this problem. I have a query by form that returns a record set in a datasheet. The user double clicks on a row and a main form (pop up) opens bound to a table with a continuous subform bound to another table. On the main form is a field called . It is vital that this is
4
3736
by: deko | last post by:
When I loop through this function, it works fine until it hits End Function - then it jumps to End Select. Very strange... This behavior occurs when Case = 255. Any ideas why this is happening? Why doesn't the function just end? Why the jump back in at End Select? Thanks in advance. Private Function GetRecurrenceType(r As Long, Optional s As Boolean) As String Dim strReturn As String
24
2467
by: MP | last post by:
vb6, dao, mdb, win2k (no access) db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'" i thought that would update all records in table it updated all but one to read them back i used "SELECT <fldlistFROM " & TABLE_NAME & " WHERE FLDMARK <'unassigned' ORDER BY <fldlist>"
2
1573
by: HeavenCore | last post by:
Hello all, i have a process which reads 2 csv files into two different tables (thus there is no real primary key etc) i need to update 1 column in one table (EnterpriseBuffer) with data from another table (EnterpriseProdGroupToAnalystDeptMap.AnalystDepartment). This is done where EnterpriseBuffer.PG = EnterpriseProdGroupToAnalystDeptMap.EnterprisePG. I attempted the following: Update EnterpriseBuffer SET...
5
2268
by: Chris Cowles | last post by:
I use an application that uses Oracle 8.1.7. All functions of the application are completed with calls to stored procedures. A data entry error occurred that caused thousands of records to be created with a consistent error in a single field. I can identify those easily records with a select statement. I'd *really* rather not have to change them all manually. I do have access to run a simple update query to correct only the field in...
2
1899
by: DuncanIdaho | last post by:
Hi Apologies if this is similar to a (very) recent post. I was wondering if it is possible to execute an update query that contains a select statement on an MS access 2000 database. I have included a detaled example at the bottom of this post I have the following update query that works as I expect it to on a MySQL database
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10678
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10751
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10367
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9512
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7914
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5740
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5940
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4153
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.