Joining temp tables with different structure

Question

I have 2 temp tables. One has data column wise.

OrderCode   OrderType
ABC         1000

One has data row wise

FieldName   Value
ORDER_CODE  ABC
ORDER_TYPE  2000

I need to write a query that essentially joins both of them and updates the value if it exists in table 2.

Recordset result needs to be

OrderCode  OrderType
ABC        2000

EDIT-

If there are 2 orders in table 1 with the same order code:

OrderCode   OrderType
ABC         1000
ABC     5000

the result set will need to be

   OrderCode   OrderType
   ABC         2000
   ABC         2000

Answer 1

To select the rowset:

SELECT  tt1.ordercode, tt2_type.Value
FROM    Table2 AS tt2_code
JOIN    Table1 tt1
ON      tt1.orderCode = tt2_code.value
JOIN
        Table2 AS tt2_type
ON      tt2_type.fieldName = 'ORDER_TYPE'
WHERE   tt2_code.fieldName = 'ORDER_CODE'

To update Table1 based on values from Table2:

WITH q AS
    (
    SELECT  tt1.ordercode, tt2_type.Value
    FROM    Table2 AS tt2_code
    JOIN    Table1 tt1
    ON      tt1.orderCode = tt2_code.value
    JOIN
            Table2 AS tt2_type
    ON      tt2_type.fieldName = 'ORDER_TYPE'
    WHERE   tt2_code.fieldName = 'ORDER_CODE'
    )
UPDATE q
SET    oldvalue = newvalue

This is one of the cases where JOIN-less syntax is more legible.