Link to home
Start Free TrialLog in
Avatar of jseneris
jseneris

asked on

Can't figure out Insert SQL command with subquery

Hi,

I'm trying to write an Insert statement in Access where the value the field is set to is determined by a subquery.  I have two tables, the target table, tblShiftDetails that has fields shiftId(integer) and Shift (datetime); and the other table is tblShift that has fields ShiftId (integer), ShiftStart and ShiftEnd.

What I want to do is:

update tblShiftDetails
set shiftId = (select ShiftId from tblShift where Shift between ShiftStart and shiftEnd)


I get an "Operation must use an updateable query" error.  
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello jseneris,

There is a possibility that your subquery returns more than one record in tblShift for each record
in tblShiftDetails, which would be a big no-no.

Regards,

Patrick
Avatar of RgGray3
RgGray3

I have found that many queries that I would LIKE to use as an update query will not allow me for the same reason.

My solution is to convert the query into a make table query, creating a temp table, then running the update using a second query and this new table.