By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Update Query Question

P: 76
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
Jun 1 '07 #1
Share this Question
Share on Google+
6 Replies


P: 76
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Table1ID
  2. FROM (Table1 INNER JOIN LinkTable ON Table1.Table1ID = LinkTable.Table1ID) INNER JOIN Table2 ON LinkTable.Table2ID = Table2.Table2ID
  3. GROUP BY Table1.Table1ID
  4. 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.
Jun 1 '07 #2

nico5038
Expert 2.5K+
P: 3,072
You can create an UPDATE query and use the IN() clause in the WHERE clause like:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblX SET Y=Z
  2. WHERE ID in (<your ID selection query>)
  3.  
Nic;o)
Jun 2 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Table1ID
  2. FROM (Table1 INNER JOIN LinkTable ON Table1.Table1ID = LinkTable.Table1ID) INNER JOIN Table2 ON LinkTable.Table2ID = Table2.Table2ID
  3. GROUP BY Table1.Table1ID
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1ID
  2. FROM LinkTable
  3. GROUP BY Table1ID
  4. HAVING Count(*)=1
Jun 2 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
You can create an UPDATE query and use the IN() clause in the WHERE clause like:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblX SET Y=Z
  2. WHERE ID in (<your ID selection query>)
  3.  
Nic;o)
Then, from Nico's idea :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1 SET Y=Z
  2. WHERE Table1ID In (SELECT Table1ID
  3.                    FROM LinkTable
  4.                    GROUP BY Table1ID
  5.                    HAVING Count(*)=1)
Jun 2 '07 #5

P: 76
Wonderful. Thank you both!
Jun 5 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
No probs Josh.
I hope the rest of the project goes smoothly for you :)
Jun 5 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.