Q:

How to convert this Access inner join query into SQL Server?

I want to write an update statement that compares the new code table to the old code by joining on the MASTER_ID.  Then If the NEW_CODE.CODE = 1234 AND OLD_CODE.CODE_ID is not in the CODE_MAPPING table then SET NEW_CODE.CODE_ID = OLD_CODE.CODE_ID AND NEW_CODE.SEQ_NO = OLD_CODE.SEQ_NO.  This is what I have so far but it does not seem to correctly update if I get a null value in the CODE_ID column.  I think I need to do a self join but nothing I do seems to work any help would be greatly appreciated.   
I have three tables with the following data:
**NEW_CODE**

    MASTER_ID     SEQ_NO     CODE_ID     CODE
    100            0          XX         1234
    200            0                     5555
    300            0          XX         1234
    300            0          XX         1234

**OLD_CODE**

   MASTER_ID   SEQ_NO   CODE_ID
   100          1        D1           
   200          1        A1
   300          1        
   300          2        Z1

**CODE_MAPPING**

   CODE_ID
   A1
   B1
   C1
   D1

UPDATE STATEMENT:
UPDATE NEW
  SET SEQ_NO = OLD.SEQ_NO,
  CODE_ID = OLD.CODE_ID

  FROM NEW_CODE       NEW
  INNER JOIN OLD_CODE      OLD
  ON NEW.MASTER_ID = OLD.MASTER_ID

 LEFT JOIN CODE_MAPPING       CM
  ON OLD.CODE_ID = CM.CODE_ID
  WHERE NEW.CODE = 1234 
  AND CM.CODE_ID IS NULL

The updated table should look like this:
 **UPDATED NEW_CODE**

        MASTER_ID     SEQ_NO     CODE_ID     CODE
        100            0          XX         1234
        200            0                     5555
        300            1                     1234
        300            2          Z1         1234

So the only thing that should update is the two rows with a MASTER_ID of 300.  But for some reason I am getting the following result instead:
 **CURRENT RESULT NEW_CODE**

        MASTER_ID     SEQ_NO     CODE_ID     CODE
        100            0          XX         1234
        200            0                     5555
        300            2          Z1         1234
        300            2          Z1         1234

A:

As you have two rows with same identifier (MASTER_ID), tsql is needed instead of a simple statement.
Precondition for rows to update:
Every row in NEW could be matched to one row in OLD and reverse. 
We need a criteria to identify updated rows in NEW. I use SEQ_NO=0 here.

a unique key for each row in OLD and NEW is needed. Your example data don't contain a unique key. So I add one to OLD and one to NEW. It can be dropped in the end.
alter table OLD add IDX int IDENTITY(1,1)
alter table NEW add IDX int IDENTITY(1,1)
declare a cursor on OLD (only rows to be updated)
DECLARE @IdxOld int
DECLARE @IdxNew int
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT idx
FROM OLD
WHERE NOT EXISTS (SELECT * FROM CODE_MAPPING CM
                WHERE OLD.CODE_ID = CM.CODE_ID)
do foreach on OLD and update NEW
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @IdxId
WHILE @@FETCH_STATUS = 0
BEGIN 
SELECT @IdxNew = Min(Idx) -- min to get only one row
  FROM NEW
  INNER JOIN OLD_CODE OLD  ON NEW.MASTER_ID = OLD.MASTER_ID
WHERE NEW.CODE = 1234 
    AND OLD.IDX = @IdxOld -- current row from old
    AND SEQ_NO=0 -- row not updated yet         

UPDATE NEW
  SET SEQ_NO = OLD.SEQ_NO,
  CODE_ID = OLD.CODE_ID
FROM NEW_CODE NEW
  INNER JOIN OLD_CODE  OLD  ON NEW.MASTER_ID = OLD.MASTER_ID
WHERE NEW.IDX = @IdxNew
  AND OLD.IDX = @IdxOld

FETCH NEXT FROM MY_CURSOR INTO @IdxId

END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
Drop IDX columns
alter table OLD drop COLUMN IDX
alter table NEW drop COLUMN IDX

