Update Query Question

[jpatchak]

Hello,

I have a database with two tables (Table1 and Table2) that are in a many to many relationship. They are linked by a link table that stores the keys from each table. I need to run a query that updates a field in each record in Table1 where there is exactly 1 linked record in Table2. Any ideas how to write an update query to accomplish this?

Thanks,
Josh

[jpatchak]

If this helps shed light on how the tables are organized, this select query gives me a list of the IDs from table 1 of the records I'd want to update:

SELECT Table1.Table1ID
FROM (Table1 INNER JOIN LinkTable ON Table1.Table1ID = LinkTable.Table1ID) INNER JOIN Table2 ON LinkTable.Table2ID = Table2.Table2ID
GROUP BY Table1.Table1ID
HAVING (((Count(Table2.Table2ID))=1));

The recordset in this query is not updatable so I can't join table1 ID on the ID in this query and run the update that way.

[nico5038]

You can create an UPDATE query and use the IN() clause in the WHERE clause like:

UPDATE tblX SET Y=Z
WHERE ID in (<your ID selection query>)

Nic;o)

[NeoPa]

If this helps shed light on how the tables are organized, this select query gives me a list of the IDs from table 1 of the records I'd want to update:

SELECT Table1.Table1ID
FROM (Table1 INNER JOIN LinkTable ON Table1.Table1ID = LinkTable.Table1ID) INNER JOIN Table2 ON LinkTable.Table2ID = Table2.Table2ID
GROUP BY Table1.Table1ID
HAVING (((Count(Table2.Table2ID))=1));

The recordset in this query is not updatable so I can't join table1 ID on the ID in this query and run the update that way.

In this case you don't need Table1 or Table2 at all. You can find all that you need simply in LinkTable.

SELECT Table1ID
FROM LinkTable
GROUP BY Table1ID
HAVING Count(*)=1

[NeoPa]

You can create an UPDATE query and use the IN() clause in the WHERE clause like:

UPDATE tblX SET Y=Z
WHERE ID in (<your ID selection query>)

Nic;o)

Then, from Nico's idea :

UPDATE Table1 SET Y=Z
WHERE Table1ID In (SELECT Table1ID
                   FROM LinkTable
                   GROUP BY Table1ID
                   HAVING Count(*)=1)

[jpatchak]

Wonderful. Thank you both!

[NeoPa]

No probs Josh.
I hope the rest of the project goes smoothly for you :)