If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update with subquery[complex]
Hi,
I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student |
#2
|
|||
|
|||
Update with subquery[complex]
Try using DLookup function.
Assumption: Team is a text field UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") If Team is a number field then change the DLookup to eliminate the text delimiters DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= " & EmpIt.Team) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... Hi, I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student |
#3
|
|||
|
|||
Update with subquery[complex]
Thanks John! I was just investigating exactly the same solution an while it
is a bit slow, it does the job! ---------------- Joost de Vries R&D Student "John Spencer" wrote: Try using DLookup function. Assumption: Team is a text field UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") If Team is a number field then change the DLookup to eliminate the text delimiters DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= " & EmpIt.Team) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... Hi, I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student |
#4
|
|||
|
|||
Update with subquery[complex]
You could probably speed it up significantly by applying some criteria to
the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... Thanks John! I was just investigating exactly the same solution an while it is a bit slow, it does the job! ---------------- Joost de Vries R&D Student "John Spencer" wrote: I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student |
#5
|
|||
|
|||
Bug in Access - Update with subquery
In SQL*Server, this works:
use jc1accts update customer set balance = (select sum(amount) from invoice where customer.c_no = invoice.c_no) In Access, you get this error: "Operation must use an updatable query" This clearly illustrates a bug in Access. John Spencer wrote: You could probably speed it up significantly by applying some criteria to the 17-Mar-08 You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... Previous Posts In This Thread: On Monday, March 17, 2008 8:00 AM JoostdeVrie wrote: Update with subquery[complex] Hi, I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student On Monday, March 17, 2008 8:43 AM John Spencer wrote: Try using DLookup function. Try using DLookup function. Assumption: Team is a text field UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") If Team is a number field then change the DLookup to eliminate the text delimiters DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= " & EmpIt.Team) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... On Monday, March 17, 2008 8:59 AM JoostdeVrie wrote: Thanks John! Thanks John! I was just investigating exactly the same solution an while it is a bit slow, it does the job! ---------------- Joost de Vries R&D Student "John Spencer" wrote: On Monday, March 17, 2008 11:47 AM John Spencer wrote: You could probably speed it up significantly by applying some criteria to the You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Joost de Vries" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint - Managing Unused or Archive sites automatically http://www.eggheadcafe.com/tutorials...aging-unu.aspx |
#6
|
|||
|
|||
Bug in Access - update with subquery
The error message in Access is:
Operation must use an updatable query John Carter wrote: Bug in Access - Update with subquery 24-Feb-10 In SQL*Server, this works: use jc1accts update customer set balance = (select sum(amount) from invoice where customer.c_no = invoice.c_no) In Access, you get this error: "Operation must use an updatable query" This clearly illustrates a bug in Access. Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint List Usage and Statistics http://www.eggheadcafe.com/tutorials...usage-and.aspx |
#7
|
|||
|
|||
Bug in Access - Update with subquery
It's just SQL that Access/JET doesn't support. You can use the much slower
DSum(): UPDATE Customer SET Balance = DSum("Amount","Invoice","C_No=" & C_No) If C_No is text: UPDATE Customer SET Balance = DSum("Amount","Invoice","C_No=""" & C_No & """") -- Duane Hookom MS Access MVP "John Carter" wrote in message ... In SQL*Server, this works: use jc1accts update customer set balance = (select sum(amount) from invoice where customer.c_no = invoice.c_no) In Access, you get this error: "Operation must use an updatable query" This clearly illustrates a bug in Access. John Spencer wrote: You could probably speed it up significantly by applying some criteria to the 17-Mar-08 You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... Previous Posts In This Thread: On Monday, March 17, 2008 8:00 AM JoostdeVrie wrote: Update with subquery[complex] Hi, I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student On Monday, March 17, 2008 8:43 AM John Spencer wrote: Try using DLookup function. Try using DLookup function. Assumption: Team is a text field UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") If Team is a number field then change the DLookup to eliminate the text delimiters DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= " & EmpIt.Team) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... On Monday, March 17, 2008 8:59 AM JoostdeVrie wrote: Thanks John! Thanks John! I was just investigating exactly the same solution an while it is a bit slow, it does the job! ---------------- Joost de Vries R&D Student "John Spencer" wrote: On Monday, March 17, 2008 11:47 AM John Spencer wrote: You could probably speed it up significantly by applying some criteria to the You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint - Managing Unused or Archive sites automatically http://www.eggheadcafe.com/tutorials...aging-unu.aspx |
#8
|
|||
|
|||
Bug in Access - Update with subquery
On Wed, 24 Feb 2010 00:21:14 -0800, John Carter wrote:
In SQL*Server, this works: use jc1accts update customer set balance = (select sum(amount) from invoice where customer.c_no = invoice.c_no) In Access, you get this error: "Operation must use an updatable query" This clearly illustrates a bug in Access. A friend of mine had a Volkswagen Beetle (back in the 70's) with a personalized license plate: "FEATURE" Yes, SQL/Server supports the above query. Access/JET (aka Access/ACE) does not. They are different dialects of SQL with different restrictions and different syntax. One of the differences is that JET considers any query containing a TOTALS operation to be non-updateable. Call it a bug if you like, but it's well and clearly documented. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Bug in Access - Update with subquery
Yes, it used to be that only Access supported modern SQL, and
features like replication and long table names and alternate character sets and declarative referential integrity. Gradually, all the other products, like Oracle and SQL Server and MySQL, caught up with, and then passed Access/Jet. Sadly, Access/Jet never got features from the other products: the influence and the feature copying was all the other way. (david) John Carter wrote in message ... In SQL*Server, this works: use jc1accts update customer set balance = (select sum(amount) from invoice where customer.c_no = invoice.c_no) In Access, you get this error: "Operation must use an updatable query" This clearly illustrates a bug in Access. John Spencer wrote: You could probably speed it up significantly by applying some criteria to the 17-Mar-08 You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... Previous Posts In This Thread: On Monday, March 17, 2008 8:00 AM JoostdeVrie wrote: Update with subquery[complex] Hi, I'm currently working on a script that will be executed when someone deletes a "team" entry from a quality control database. The database contains teams, employees and samples (each employee belongs to a team and for each employee samples can be registered). Each sample has two references to the empolyees table: "employee" and "registrant" (always two different id's). What I want to achieve now is that when a team is deleted, all samples that have a registrant of the deleted team linked will now link to a so-called "expired registrant" from their own team. My query now looks like this: UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.empolyee_id SET samples.registrant=(SELECT TOP 1 employee_id FROM employees WHERE last_name="** EXPIRED **" AND team=emplT.team) When I try to execute this I get the error that "The query must be updatable"... Any ideas how to sove this? ---------------- Joost de Vries R&D Student On Monday, March 17, 2008 8:43 AM John Spencer wrote: Try using DLookup function. Try using DLookup function. Assumption: Team is a text field UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") If Team is a number field then change the DLookup to eliminate the text delimiters DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= " & EmpIt.Team) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... On Monday, March 17, 2008 8:59 AM JoostdeVrie wrote: Thanks John! Thanks John! I was just investigating exactly the same solution an while it is a bit slow, it does the job! ---------------- Joost de Vries R&D Student "John Spencer" wrote: On Monday, March 17, 2008 11:47 AM John Spencer wrote: You could probably speed it up significantly by applying some criteria to the You could probably speed it up significantly by applying some criteria to the query so that only selected records get updated. I would suggest something for the criteria, but I don't understand your data structure fully. UPDATE samples INNER JOIN employees As emplT ON samples.employee=emplT.employee_id SET samples.registrant = DLookup("Employee_ID", "Employees", "Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """") -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . "Joost de Vries" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint - Managing Unused or Archive sites automatically http://www.eggheadcafe.com/tutorials...aging-unu.aspx |
Thread Tools | |
Display Modes | |
|
|