473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Syntax error in update?

SG
Hi all,
I have just written a sql statement where I want two fields updated
from another table. The statement like so:
update kpidmatter
set clientcode,clientname = (select clientcode, clientname
from clientconversion
where (clientcode = kpidmatter.clintcode))
where exists
(select clientcode, clientname
from clientconversion
where (clientcode = kpidmatter.clintcode))

This refuses to work.

The statement I based this on worked (below) but only updated one
field (teach me for being too ambitions!). Can anyone see anything
obvious? I know that a DDL and sample data is far more useful but in
this case I think there's a simple syntax problem.
I'd be most grateful if someone could spot my error.

Sam

Working sample:
UPDATE kpidmatter
SET clientpartner = (SELECT ContactName
FROM newnames
WHERE (feeearnercode = kpidmatter.clientpartnercode))
where exists
(SELECT ContactName
FROM newnames
WHERE (feeearnercode = kpidmatter.clientpartnercode))
Jul 23 '05 #1
6 1274
>> I have just written a sql statement where I want two fields [sic]
updated from another table. The statement like so: .. <<

If it makes you feel better, you have correctly guessed Standard SQL-99
syntax. Microsoft just does not use it yet.

You can split the column updates into two SET clauses:

UPDATE Kpidmatter
SET client_code
= (SELECT client_code
FROM ClientConversion AS C1
WHERE C1.client_code = Kpidmatter.client_code),
client_name
= (SELECT C2.client_name
FROM ClientConversion AS C2
WHERE C2.client_code = Kpidmatter.client_code)
WHERE EXISTS
(SELECT *
FROM ClientConversion AS C3
WHERE C3.client_code = kpidmatter.client_code);

But on inspection, the first SET clause is redundant!

UPDATE Kpidmatter
SET client_name
= (SELECT C2.client_name
FROM ClientConversion AS C2
WHERE C2.client_code = Kpidmatter.client_code)
WHERE EXISTS
(SELECT *
FROM ClientConversion AS C3
WHERE C3.client_code = kpidmatter.client_code);

Jul 23 '05 #2
SG (sg*****@yahoo.ie) writes:
I have just written a sql statement where I want two fields updated
from another table. The statement like so:
update kpidmatter
set clientcode,clientname = (select clientcode, clientname
from clientconversion
where (clientcode = kpidmatter.clintcode))
where exists
(select clientcode, clientname
from clientconversion
where (clientcode = kpidmatter.clintcode))

This refuses to work.


As Joe Celko pointed out, SQL Server does not support this syntax,
although it's part of ANSI SQL.

If we disregard the fact that assignment of clientcode is redudant,
here is the most effective way of writing the above in SQL Server:

update kpidmatter
set clientcode = c.clientcode
clientname = c.clientname
from kpidmatter k
join clientconversion c ON c.clientcode = k.clintcode

This uses an extension of the UPDATE statement which is proprietary to
SQL Server and Sybase, so if you are in for portable code, you should
not use it. But apart from that it's powerful, and often gives the best
query plan in SQL Server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
SG
many thanks both of you.
Sam
Jul 23 '05 #4
Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn*********************@127.0.0.1:
If we disregard the fact that assignment of clientcode is redudant,
here is the most effective way of writing the above in SQL Server:

update kpidmatter
set clientcode = c.clientcode
clientname = c.clientname
from kpidmatter k
join clientconversion c ON c.clientcode = k.clintcode


I have just got code working that is very similar and suspect that the
explicit join is redundant. I think the following should work:

update kpidmatter
set clientcode = c.clientcode,
clientname = c.clientname
from clientconversion c
where c.clientcode = kpidmatter.clintcode
Jul 23 '05 #5
Chris Cheney (cjc1@nospam%ucs.cam.ac.uk%no%spam%please) writes:
Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn*********************@127.0.0.1:
update kpidmatter
set clientcode = c.clientcode
clientname = c.clientname
from kpidmatter k
join clientconversion c ON c.clientcode = k.clintcode


I have just got code working that is very similar and suspect that the
explicit join is redundant. I think the following should work:

update kpidmatter
set clientcode = c.clientcode,
clientname = c.clientname
from clientconversion c
where c.clientcode = kpidmatter.clintcode


This should be the same thing. The advantage with the syntax I used is
that you can use an alias for the table being updated. It also has the nice
property, that it's easy to convert the UPDATE statement to a SELECT
statement for debugging purposes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn**********************@127.0.0.1:
Chris Cheney (cjc1@nospam%ucs.cam.ac.uk%no%spam%please) writes:
Erland Sommarskog <es****@sommarskog.se> wrote in
news:Xn*********************@127.0.0.1:
update kpidmatter
set clientcode = c.clientcode
clientname = c.clientname
from kpidmatter k
join clientconversion c ON c.clientcode = k.clintcode


I have just got code working that is very similar and suspect that the
explicit join is redundant. I think the following should work:

update kpidmatter
set clientcode = c.clientcode, clientname = c.clientname
from clientconversion c where c.clientcode =
kpidmatter.clintcode


This should be the same thing. The advantage with the syntax I used is
that you can use an alias for the table being updated. It also has the
nice property, that it's easy to convert the UPDATE statement to a
SELECT statement for debugging purposes.


Yes, that's a real neat advantage.
Jul 23 '05 #7

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

Similar topics

3
by: Robert Mark Bram | last post by:
Hi All! I have the following two methods in an asp/jscript page - my problem is that without the update statement there is no error, but with the update statement I get the following error: ...
7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
2
by: Phil Powell | last post by:
I am not sure why this is producing a SQL Server related error, but w/o having an instance of SQL Server on my machine to verify anything further, can you all help me with this? <!--- validate()...
5
by: S.Patten | last post by:
Hi, I have a problem with updating a datetime column, When I try to change the Column from VB I get "Incorrect syntax near '942'" returned from '942' is the unique key column value ...
4
by: Bob Stearns | last post by:
The statement: merge into nullid.animals_et_in t1 using is3.animals t2 on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and t1.sire_regnum=t2.regnum when matched then update set...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
1
by: amitbadgi | last post by:
HI i am getting the foll error while conv an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Source Error: Line...
3
by: Jerry | last post by:
Well, here is some weirdness. First, I noticed that I have 2 Set keywords (silly me). so I removed the 2nd "Set" but still got a syntax error. Then I removed the Where clause, and now it works...
2
by: technocraze | last post by:
Hi guys, I have encountered this error when updating the values to the MS Acess table. Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
3
by: phobia1 | last post by:
Hi once again. We have just changed our ISP and things that worked fine now do not, Obviously its in the differences of MYSQL and PHP versions. Have fixed most of the problems but this UPDATE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.