I need to update a table using a query to reference the the records to be updated. The problem is that on the table there might be multiple record of the instance I need to update. I need to update just one and change it's status to SALE. I use a select query to retrieve a single record; this I'm able to do without problem. My problem starts when I need to combine that with the UPDATE. I use the query as the condition to the update without success. I I run the query by itself it would retrieve the appropriate data let's say for example to records; when combined I get the message that I'm to update let say 500 records! 
Obviously I'm not doing it right. Here is the code I'm using:
UPDATE CALL_REPORT SET CALL_REPORT.STATUS = "SALE"
WHERE EXISTS
(SELECT SalesnotinCALL_REPORT.[Session id], First(CALL_REPORT.Status) AS FirstOfStatus
FROM SalesnotinCALL_REPORT LEFT JOIN CALL_REPORT ON SalesnotinCALL_REPORT.[Session id] = CALL_REPORT.SessionID
GROUP BY SalesnotinCALL_REPORT.[Session id]);
I also tried a completely different approach by creating a FlagTable were I'll add the id of a record that was updated and when updating checking that if the ID exist then it will flag that one instance was already updated. But it didn't work either. It points out ambiguous outer joins.
UPDATE SalesnotinCALL_REPORT LEFT JOIN (CALL_REPORT RIGHT JOIN FlagTable ON CALL_REPORT.SessionID = FlagTable.Sessio_ID) ON SalesnotinCALL_REPORT.[Session id] = CALL_REPORT.SessionID SET CALL_REPORT.Status = "SALE", FlagTable.Sessio_ID = [CALL_REPORT].[SessionID]
WHERE (((FlagTable.Sessio_ID) Is Null) AND ((SalesnotinCALL_REPORT.[Session id])=[CALL_REPORT].[SessionID]));
I'll appreciate if someone could give me a hint on how to do this right.
Thanks
Obviously I'm not doing it right. Here is the code I'm using:UPDATE CALL_REPORT SET CALL_REPORT.STATUS = "SALE"
WHERE EXISTS
(SELECT SalesnotinCALL_REPORT.[Session id], First(CALL_REPORT.Status) AS FirstOfStatus
FROM SalesnotinCALL_REPORT LEFT JOIN CALL_REPORT ON SalesnotinCALL_REPORT.[Session id] = CALL_REPORT.SessionID
GROUP BY SalesnotinCALL_REPORT.[Session id]);
I also tried a completely different approach by creating a FlagTable were I'll add the id of a record that was updated and when updating checking that if the ID exist then it will flag that one instance was already updated. But it didn't work either. It points out ambiguous outer joins.
UPDATE SalesnotinCALL_REPORT LEFT JOIN (CALL_REPORT RIGHT JOIN FlagTable ON CALL_REPORT.SessionID = FlagTable.Sessio_ID) ON SalesnotinCALL_REPORT.[Session id] = CALL_REPORT.SessionID SET CALL_REPORT.Status = "SALE", FlagTable.Sessio_ID = [CALL_REPORT].[SessionID]
WHERE (((FlagTable.Sessio_ID) Is Null) AND ((SalesnotinCALL_REPORT.[Session id])=[CALL_REPORT].[SessionID]));
I'll appreciate if someone could give me a hint on how to do this right.
Thanks
Last edited:
