SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dimensions (
  "id" INTEGER,
  "height" INTEGER,
  "width" INTEGER,
  "length" INTEGER
);

INSERT INTO dimensions
  ("id", "height", "width", "length")
VALUES
  ('1', '10', '10', '12'),
  ('2', '10', '12', '12');
2 rows affected

CREATE TABLE purchases (
  "order_number" INTEGER,
  "height" INTEGER,
  "width" INTEGER,
  "length" INTEGER,
  "dimension_id" VARCHAR(4)
);

INSERT INTO purchases
  ("order_number", "height", "width", "length", "dimension_id")
VALUES
  ('1', '10', '12', '10', NULL),
  ('2', '10', '12', '12', NULL),
  ('3', '10', '12', '15', NULL),
  ('4', '12', '10', '10', NULL),
  ('4', '10', '10', '12', NULL);
5 rows affected

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)
  Or 
  (dimensions.height=o.Length AND dimensions.width=o.width AND dimensions.length=o.height)
 
4 rows affected

SELECT * FROM purchases


order_number
height
width
length
dimension_id


1
10
12
10
1

2
10
12
12
2

3
10
12
15
null

4
12
10
10
1

4
10
10
12
1