On Mon, 18 Jun 2007 23:54:10 +1000, you wrote:
>So, my question remains, is it possible to update multiple columns
>from a single related row in another table, without having to perform
>multiple redundant WHERE clauses?
You may want to introduce a sold_products table, one row per
product-incarnation, only containing products you actually sold,
1 : n with the sales_products table, which would only contain
sale_id and a foreign key pointing to sold_products, not the
product properties at the moment of sale.
The same row in sold_products could be used by other sales where
the exact same product incarnation is sold. sold_products
removes the redundancy your solution still has.
I wouldn't worry too much about multiple redundant WHERE
clauses, because the row would still be in cache and found
immediately by product_id.
But i agree, what we seem to miss sometimes is an expression to
transfer a column list from a subquery to some outer SQL
contruct for SETting or comparison purposes.
If they existed your trigger action
UPDATE sale_products
SET
buy = (SELECT buy FROM products WHERE
products.product_id =
NEW.product_id)
, sell = (SELECT sell FROM products WHERE
products.product_id =
NEW.product_id)
, desc = (SELECT desc FROM products WHERE
products.product_id =
NEW.product_id)
WHERE ...
could be expressed by:
SET (buy,sell,desc) = (
SELECT buy,sell,desc
FROM products
WHERE products.product_id = NEW.product_id
)
WHERE ...
See also:
http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql
item 2005.10.06
--
( Kees Nuyt
)
c[_]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
