Match two sets of columns that contain same elements regardless of order

I have two tables named purchases and dimensions, both contains these three separate columns height, length, width. In the dimensions table there are about 120 unique combinations of dimensions as shown below. (regardless of the order, so for example 10,10,12 won’t exist if there is already 10,12,10).

In purchases the dimensions are stored but could be in a different order. (For example it’s stored 10,10,12 in dimensions but could be stored as 10,12,10 or 12,10,10 in purchases.)

All methods I found ignores duplicates, but since 10,10,12 should be consider different from 10,12,12, I can’t figure out how to adapt those solutions.

Is there a way to use SQL to update each purchase’s dimension_id? (Must leave it NULL if not exist dimension combination.)

dimensions

    id    height    width    length
    1     10        10       12
    2     10        12       12


purchases

    order_number    height    width    length    dimension_id
    1               10        12       10        NULL  <- to 1
    2               10        12       12        NULL  <- to 2
    3               10        12       15        NULL  <- still NULL

Edit:
Renamed orders to purchases to avoid ambiguousness between table name and the "order" of data.

The version I’m using is Microsoft SQL Server 2017.

>Solution :

As you have only three dimensiomns you don’t need fancy pivot or something else

This query would immensely profit from an in dex on height, wdth and length

UPDATE o
  SET o.dimension_id = dimensions.id
  FROM purchases o INNER JOIN dimensions ON 
  (dimensions.height=o.height AND dimensions.width=o.width AND dimensions.length=o.Length)
  OR
  (dimensions.height=o.width AND dimensions.width=o.Length AND dimensions.length=o.height)
  OR
  (dimensions.height=o.Length AND dimensions.width=o.height AND dimensions.length=o.width)
 

4 rows affected

SELECT * FROM purchases

fiddle