Joining temp tables with different structure

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

From stackoverflow

• 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.

  DotnetDude : In the FROM clause, what is tt2?

  Lieven : Me thinks that should be Table2

  DotnetDude : Cool, will this query work in the 2nd scenario (Pls see Edit)

  Quassnoi : Yes, this will work.

  DotnetDude : Quassnoi - I am trying to rewrite using INNER JOINs and am unable to get it work. Could you give me an ex of how to do this using the INNER JOIN syntax?

  Quassnoi : See updated post

  DotnetDude : Quassnoi - SQL Server 2008 will have the joinless syntax obsolete. Using the join syntax is one of our dev guidelines. Any help is appreciated

  Quassnoi : WHERE will not be obsolete even in SQL Server 2028, if it will still be called _SQL_ Server. How will you filter the rows if you don't have any joins? There are cases when you can't completely get rid of WHERE, this being one of these cases.

  Quassnoi : You may put a CROSS JOIN instead of comma between Table1 and Table2 if you want, but I don't see any point in this.

  Quassnoi : See updated post.