473,372 Members | 1,314 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,372 software developers and data experts.

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
AddressDescription of Entity 456 = AddressDescription 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 11305
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 <OriginalTableName>_1.
Then you do a conventional Update query.

On Thu, 30 Dec 2004 10:20:12 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> 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
AddressDescription of Entity 456 = AddressDescription 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.net
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 <OriginalTableName>_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.Entity_ID = search2150.Entity_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.Entity_ID =
search2110.Entity_ID SET tblEntity.Cat_ID = 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.SearchQry( [parameters] )
Set qItem = db.CreateQueryDef(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.EmployeeID, Employees.LastName, Employees.Address,
Employees.ReportsTo
FROM Employees;
A self-join query:
SELECT Employees.EmployeeID, Employees.LastName, Employees.Address,
Employees.ReportsTo, Employees_1.LastName AS SupLastName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.ReportsTo = Employees_1.EmployeeID;
A self-join query with criterion
SELECT Employees.EmployeeID, Employees.LastName, Employees.Address,
Employees.ReportsTo, Employees_1.LastName AS SupLastName
FROM Employees INNER JOIN Employees AS Employees_1 ON
Employees.ReportsTo = Employees_1.EmployeeID
WHERE (((Employees_1.LastName)="Fuller"));
An update query for the self-join query:
UPDATE Employees INNER JOIN Employees AS Employees_1 ON
Employees.ReportsTo = Employees_1.EmployeeID SET Employees.Address =
[Employees].[Address] & "xxx"
WHERE (((Employees_1.LastName)="Fuller"));


On Fri, 31 Dec 2004 00:18:32 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> 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 <OriginalTableName>_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.Entity_ID = search2150.Entity_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.net
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
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...
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 ...
1
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...
17
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...
4
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...
7
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" ...
19
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...
8
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
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...
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.