473,735 Members | 3,971 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE query syntax question

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
AddressDescript ion of Entity 456 = AddressDescript ion of Entity_ID 123
Address1 of Entity 456 = Address1 of Entity_ID 123
City of Entity 456 = City of Entity_ID 123
[and so on]

What is the correct syntax to do this?

Thanks in advance.
Nov 13 '05 #1
4 11336
What connects records #123 and #456?

You can do a self-join -- a table can be joined to itself just as if
it was an independent table. Simply add a second copy of the table to
the Query grid. By default it will be called <OriginalTableN ame>_1.
Then you do a conventional Update query.

On Thu, 30 Dec 2004 10:20:12 GMT, "deko"
<www.clearpoint systems.com@use _contact_form.c om> wrote:
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
AddressDescrip tion of Entity 456 = AddressDescript ion of Entity_ID 123
Address1 of Entity 456 = Address1 of Entity_ID 123
City of Entity 456 = City of Entity_ID 123
[and so on]

What is the correct syntax to do this?

Thanks in advance.

*************** *******
ja************* *@telusTELUS.ne t
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #2
> You can do a self-join -- a table can be joined to itself just as if
it was an independent table. Simply add a second copy of the table to
the Query grid. By default it will be called <OriginalTableN ame>_1.
Then you do a conventional Update query.


Thanks for the reply. I tried this, but no luck:

UPDATE tblEntity SET Cat_ID = 82 INNER JOIN search2150 ON
tblEntity.Entit y_ID = search2150.Enti ty_ID

"Error Number 3144: Syntax error in UPDATE statement."

This is a little different scenario from my original post, but the idea is
still to do a join in an update query. Originally, my SQL statement looks
like this:

UPDATE tblEntity SET Cat_ID = 82 WHERE Entity_ID IN (SELECT DISTINCT
Entity_ID FROM search2150)

but it runs very slowly so I'm trying to get rid of the nested query. The
idea is to set the Cat_ID in tblEntity to 82 for every Entity_ID found in
query search2150. I'm still not sure what the syntax should look like. Can
you provide an example of how to do a join in an Update query?

Thanks!
Nov 13 '05 #3
Here's another crack at it:

UPDATE tblEntity INNER JOIN search2110 ON tblEntity.Entit y_ID =
search2110.Enti ty_ID SET tblEntity.Cat_I D = 81

This time I get this error:

"Error Number 3073: Operation must use an updateable query."

The search2110 query is built with code:

Dim db As DAO.Database
Dim qItem As QueryDef
strSql = modBuildQry.Sea rchQry( [parameters] )
Set qItem = db.CreateQueryD ef(strSearchQry , strSql)

but I see no difference between it and other queries.
Nov 13 '05 #4
From the Northwind database.
A simple query:
SELECT Employees.Emplo yeeID, Employees.LastN ame, Employees.Addre ss,
Employees.Repor tsTo
FROM Employees;
A self-join query:
SELECT Employees.Emplo yeeID, Employees.LastN ame, Employees.Addre ss,
Employees.Repor tsTo, Employees_1.Las tName AS SupLastName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.Repor tsTo = Employees_1.Emp loyeeID;
A self-join query with criterion
SELECT Employees.Emplo yeeID, Employees.LastN ame, Employees.Addre ss,
Employees.Repor tsTo, Employees_1.Las tName AS SupLastName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.Repor tsTo = Employees_1.Emp loyeeID
WHERE (((Employees_1. LastName)="Full er"));
An update query for the self-join query:
UPDATE Employees INNER JOIN Employees AS Employees_1 ON
Employees.Repor tsTo = Employees_1.Emp loyeeID SET Employees.Addre ss =
[Employees].[Address] & "xxx"
WHERE (((Employees_1. LastName)="Full er"));


On Fri, 31 Dec 2004 00:18:32 GMT, "deko"
<www.clearpoint systems.com@use _contact_form.c om> wrote:
You can do a self-join -- a table can be joined to itself just as if
it was an independent table. Simply add a second copy of the table to
the Query grid. By default it will be called <OriginalTableN ame>_1.
Then you do a conventional Update query.


Thanks for the reply. I tried this, but no luck:

UPDATE tblEntity SET Cat_ID = 82 INNER JOIN search2150 ON
tblEntity.Enti ty_ID = search2150.Enti ty_ID

"Error Number 3144: Syntax error in UPDATE statement."

This is a little different scenario from my original post, but the idea is
still to do a join in an update query. Originally, my SQL statement looks
like this:

UPDATE tblEntity SET Cat_ID = 82 WHERE Entity_ID IN (SELECT DISTINCT
Entity_ID FROM search2150)

but it runs very slowly so I'm trying to get rid of the nested query. The
idea is to set the Cat_ID in tblEntity to 82 for every Entity_ID found in
query search2150. I'm still not sure what the syntax should look like. Can
you provide an example of how to do a join in an Update query?

Thanks!

*************** *******
ja************* *@telusTELUS.ne t
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #5

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

Similar topics

4
18180
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no configuration changed. Until yesterday, I re-ran it again and it took me more than 18hrs and still not yet finished!!! What's wrong with it? I can ran it successfully before. I have tried two times but the result was still the same.
5
5136
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 However if I update any other column the syntax is fine
1
11113
by: avinash | last post by:
hi myself avi i am developing one appliacaion in which i am using vb 6 as front end, adodb as database library and sql sever 7 as backend. i want to update one table for which i required data from other table. and iretrive data from second table by giving some condition. when i get data, then to update first table i need to use do while loop. instead of that i want to use select statement directly in update query. plz give me some help....
17
5024
by: kalamos | last post by:
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)
4
2256
by: dp | last post by:
After looking and looking, it appears that Access ADPs graphic query designer won't display: update customer set = . from customer, where customer. = .; It comes up with the "Query Definitions Differ" dialog box. Anybody know anything about this? I can live this with I guess, however it was sure
7
3531
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" Transactions = name of the table I want to update balance = name of the field i want to update daily balance= name of the query result that I want to move to the table
19
8378
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without FOR UPDATE, it is fine and no error. I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF EMPNO"), but it still couldn't help. any ideas? I tried to search in the web but couldn't find similar
8
2530
by: Richard | last post by:
Hello! I have this piece of SQL code: UPDATE a SET Field1 = c.Field1 FROM a INNER JOIN b ON a.GUID1 = b.GUID1 INNER JOIN c ON b.GUID2 = c.GUID2 WHERE c.Type = 1
5
2062
by: teddysnips | last post by:
Having upsized my client's back-end DB to SQL Server, the following query does not work ("Operation must use an updateable query"). UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.TransFromID = tblREFUNDS.BookingID SET tblREFUNDS. = tblbookings! bookingid WHERE (((tblREFUNDS.)=0) AND ((tblbookings.TransFromID) Is Not Null)); I tried rewriting it as follows, with the same problem:
0
8964
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8202
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
6747
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
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4562
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
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3274
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2740
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.