T&B wrote:
In case the original question has been clouded by discussions of
running a general store and the arson habits of the Hand Crafted
Guild, allow me to crystalize my purely SQLite question:
I know I can update via:
update Table1
set
c1 = (select d1 from Table2 where Table2.id = desired_id)
, c2 = (select d2 from Table2 where Table2.id = desired_id)
, c3 = (select d3 from Table2 where Table2.id = desired_id)
, cn = (select dn from Table2 where Table2.id = desired_id)
where
rowid = desired_rowid
But that executes the same where clause n times, so scans through
Table2 for a to find the same matching row n times.
Is it possible to construct an update that executes a where clause
once to locate all of the desired columns?
Thanks,
Tom
Tom,
What you need is a select that returns all the relevant data from table2
(your current product info table) and the existing data that was
inserted as the new records was added to your sales history table. This
can be done using a replace command instead of an update in a after
insert trigger.
Using the schema name from earlier posts, I would suggest something like
this.
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
buy REAL,
sell REAL,
desc TEXT
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
customer_id INTEGER -- FK to customes table
);
CREATE TABLE sale_products (
sale_id INTEGER, -- FK to sales table
product_id INTEGER, -- FK to products table
buy REAL,
sell REAL,
desc TEXT
);
create trigger copy_product_info
after insert on sale_products
begin
replace into sale_products(sale_id, product_id, buy, sell, desc)
select s.sales_id, null, p.buy, p.sell, p.desc
from sale_products as s
join products as p on s.product_id = p.product_id
where s.sale_id = new.id;
end;
-- insert records for a single sale
insert into sales values(1001, 2001);
insert into sale_products(1001, 15083);
insert into sale_products(1001, 23069);
insert into sale_products(1001, 25655);
This uses random data, but I hope you get the idea. Th etriger copies
all the required fields from the product table at the time the record is
inserted into the history table. If you copy everything of interest, you
should never need to refer to the product table again, and changes to
the product table won't affect your sales history.
Note, that the product_id that is stored in the sale_product table
should not be need after the trigger has executed, so it is replace by a
null to ensure that it can't inadvertently be used to link to the
product table and pull out data that may have changed since the history
record was created. If you wan to maintain the product_id value, you
should change the null in the trigger's select to s.product_id
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
