UPDATE query syntax question

deko

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

Jack MacDonald

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

deko

> 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

deko

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

Jack MacDonald

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