Hi,

I am attempting to write the quivalent of the following UPDATE query using Elevate SQL.
Examples I have found in the forum don't cover the extra JOIN and multi-field relationship.

Can you please provide some tips.

UPDATE Product SET X_CategoryID = c.CategoryID
FROM Product p
INNER JOIN ProductCategory pc ON pc.Key1 = p.Key1 AND pc.Key2 = p.Key2
INNER JOIN Category c ON c.CategoryID = pc.CategoryID
WHERE c.Type = 'X'

Regards

Greg

Greg,

If you can assume that a product doesn't have more than one category of type
X, then:

UPDATE Product p SET X_CategoryID =
   (
     SELECT
       CategoryID
     FROM
       ProductCategory pc JOIN Category c ON c.CategoryID = pc.CategoryID
     WHERE
       c.Type = 'X' and pc.Key1 = p.Key1 AND pc.Key2 = p.Key2
   )

If the same product can have more than one category of type X then you must
set some rule to decide what X_Category to pick, for example the minimum:
....
(
  SELECT
    Min(CategoryID)
  FROM
....

--
Fernando Dias
[Team Elevate]