Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
UPDATE with multiple JOINs |
Tue, Dec 2 2008 11:36 PM | Permanent Link |
Greg | 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 |
Wed, Dec 3 2008 6:16 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |