Update Query With Two Joins I am struggling to get my syntax correct for this update query.  I need to update Sanity.itemname based off employeecodes in SSL12.LBS  I can join to that table like so Sanity.cID = Helper1.cID and Helper1.LBS = SSL12.LBS but the issue I keep running into with my queries is that none of the tables hold true primary keys and my SWITCH() statement seems to be ignored and ALL cID are updated.

Below is sample DDL - can someone help me come to an update query that will only update the values in my SWITCH() statement, but ONLY for LBS that exist in SSL12

Create Table SSL12
(
   LBS int
  ,fname varchar(40)
) ;

Insert Into SSL12 (LBS, fname) VALUES
  (20, 'Blue'),
  (40, 'Red'),
  (60, 'Green') ;

Create Table Helper1
(
   cID int
  ,LBS int
  ,fname varchar(40)
) ;

Insert Into Helper1 (cID, LBS, fname) VALUES
  (2222, 20, 'Blue'),
  (4212, 40, 'Red'),
  (8132, 60, 'Green') ;

Create Table Sanity
(
   cID int
  ,itemname varchar(100)
) ;

Insert Into Sanity (cID, itemname) VALUES
  (2222, 'purple flower'),
  (2222, 'foghorn'),
  (2222, 'socks'),
  (4212, 'iguana'),
  (4212, 'camel'),
  (8132, 'red curtains') ;


And the UPDATE:

UPDATE [SSL12] 
    INNER JOIN (Helper1 INNER JOIN Sanity ON Helper1.cID = Sanity.cID) 
    ON [SSL12].[LBS] = Helper1.LBS 
SET Sanity.itemname = Switch("itemname"="purple flower", "lilac", 
                             "itemname"="red flower", "rose",
                             "itemname"="yellow flower", "daisy");
The big problem here is the join between Helper1 and Sanity that causes multiple duplicates, so with larger result sets this is not going to perform well. It is possible to rewrite the update statement so that it does what you need using a CASE expression, rather than a switch. 

UPDATE s
SET itemname =
        CASE s.itemname WHEN 'purple flower' THEN 'lilac'
                        WHEN 'red flower' THEN 'rose'
                        WHEN 'yellow flower' THEN 'daisy'
             ELSE s.itemname
        END
FROM dbo.Sanity s 
    JOIN dbo.Helper1 h ON h.cID = s.cID
    JOIN dbo.SSL12 ss ON ss.LBS = h.LBS ;


Which leaves the data in the Sanity table as:

cID     itemname
----    --------
2222    lilac
2222    foghorn
2222    socks
4212    iguana
4212    camel
8132    red curtains