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