Multiple updates in single query

[wish24bone]

All,
How can I run multiple update statements that join to other tables, using 1 sql script? Here is the query I have now that is failing.. Ideas?

UPDATE EXCEPTION SET [exception].[assigned to] = [UserInfo].[Name]
IIF( [exception].[Assigned_to_id] = [UserInfo].[id]),
IIF( [exception].[Assigned_to_id] = [UserInfo2].[id]),
IIF( [exception].[Assigned_to_id] = [UserInfo3].[id]);

Thanks in advance

 

[MSAccessRookie]

All,
How can I run multiple update statements that join to other tables, using 1 sql script? Here is the query I have now that is failing.. Ideas?

UPDATE EXCEPTION SET [exception].[assigned to] = [UserInfo].[Name]
IIF( [exception].[Assigned_to_id] = [UserInfo].[id]),
IIF( [exception].[Assigned_to_id] = [UserInfo2].[id]),
IIF( [exception].[Assigned_to_id] = [UserInfo3].[id]);

Thanks in advance

Normally, I would suggest you look into building a WHERE Clause to handle the multiple conditions, but in this case, that would probably not be enough. You update a value in the Table [exception] with a value from Table [UserInfo], based on values from three tables ([UserInfo], [UserInfo2], and [UserInfo3]) All of these must be accounted for in the Query. Your conditions might best be handled in a SubQuery of type UNION.

Something like this (UNTESTED CODE) could be a good start

UPDATE (([exception] INNER JOIN [UserInfo] ON [exception].[Assigned_to_id] = [UserInfo].[id]) INNER JOIN IDstoUpdate ON [exception].[Assigned_to_id] = [IDstoUpdate].[id])
SET [exception].[assigned to] = [UserInfo].[Name];
 
QUERY IDstoUpdate

SELECT [exception].[Assigned_to_id] FROM [exception] INNER JOIN [UserInfo] ON [exception].[Assigned_to_id] = [UserInfo].[id])
UNION
SELECT [exception].[Assigned_to_id] FROM [exception] INNER JOIN [UserInfo2] ON [exception].[Assigned_to_id] = [UserInfo2].[id])
UNION
SELECT [exception].[Assigned_to_id] FROM [exception] INNER JOIN [UserInfo3] ON [exception].[Assigned_to_id] = [UserInfo3].[id])