Re: [sqlite] Trigger update of multiple columns

[Ed Pasma]

Hi,
I tried to update a list of columns:
UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE ..
but this syntax is not accepted as you probably already know.
I may promote [INSERT OR] REPLACE then. It is syntactically described  
in the SQLite documentation but for the semantics you may see the  
original MySQL doc.
 http://dev.mysql.com/doc/refman/5.0/en/replace.html
It is the only way that I see to do the update with only a single  
scan of the product table.

But may be REPLACE causes troubles in combination with triggers.  
Because indirectly it performs a DELETE and a new INSERT. Other  
suggestions should be welcome.

Regards, Ed



Op 17-jun-2007, om 10:00 heeft T&B het volgende geschreven:

Hi All,

I have a pretty standard sales tracking database consisting of tables:

Products      - Each row is a product available for sale.
                Includes fields: Code, Buy, Sell, Description

Sales         - Each row is a sale made to a customer.
                Includes fields: Ref, Customer

Sale_Products - Each row is an product (many) included in a sale  
(one).
                Includes fields: Sale_Ref, Code, Buy, Sell,  
Description

Now, when I add a new Sale_Products row and assign a product Code  
to it, I want to trigger it to auto enter the Buy and Sell prices,  
and the description, by looking up the related Product (ie where  
Sale_Products.Code = Products.Code)

How can I do this?

I have something like this:

create trigger Update_Sale_Products_Code
after update of Code
on Sale_Products
begin
  update Sale_Products
    set
      Buy = (select Buy from Products where Products.Code = new.Code)
    , Sell = (select Sell from Products where Products.Code =  
new.Code)
    , Description = (select Description from Products where  
Products.Code = new.Code)
  where
        rowid=new.rowid
  ;
end

It works, but it's unnecessarily slow, since it takes a while to  
look up the huge Products table (which is actually a UNION ALL of  
various supplier catalogs), and it's looking it up for each  
updating field (and I have more fields to lookup than shown in this  
example). It would be more efficient to look it up once to find the  
corresponding product (according to Products.Code = new.Code), but  
I'm stumped as to how to do that.

I tried:

create trigger Update_Sale_Products_Code
after update of Code
on Sale_Products
begin
  update Sale_Products
    set
      Buy = (select Buy from Products)
    , Sell = (select Sell from Products)
    , Description = (select Description from Products)
  where
        rowid=new.rowid
    and Products.Code = new.Code
  ;
end

But that fails, and seems a bit ambiguous anyway. It seems to need  
some kind of JOIN, but I can't see provision for it in the UPDATE  
syntax.

There must be a much simpler way that I'm overlooking. Please  
enlighten me.

Thanks,
Tom


---------------------------------------------------------------------- 
-------
To unsubscribe, send email to [EMAIL PROTECTED]
---------------------------------------------------------------------- 
-------





-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------