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
Hi Ed,
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.
Thanks for your very thoughtful reply. That is exactly the type of
syntax I'm after, with only one executed WHERE clause
I want to insert the transactions data (product_id, buy, sell, desc) into
the sale_products table. But I want a mechanism whereby if I enter the
product_id, then the buy, sell, desc columns are auto entered (copied)
from their corresponding row in the products table.
Given:
CREATE TABLE
al Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 18, 2007 9:58 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Trigger update of multiple columns
>
>
> If you can automatically enter data then you are violating the
> normalization r
Hi Gerry,
keep each version of each product's description in the products
table, along with the date that description became valid. Then the
product ID and date in each transaction would reference the
appropriate product table data.
In certain circumstances, I can see how that would be
T wrote:
Yes, that's what I'm doing. I just want to make it more efficient.
Technically it's the sale_products table (since each sale has many
products etc), but yes, I want to insert the transactions data
(product_id, buy, sell, desc) into the sale_products table. But I want
a mechanism
If you can automatically enter data then you are violating the
normalization rules. Maybe you should get a book on database design and
become familiar with some of the fundamentals.
T wrote:
Hi John,
You have a reference data set which is accessed to get the current
value of reference
Hi John,
You have a reference data set which is accessed to get the current
value of reference elements and store transactions to record
events. The transaction trails provide event history.
Yes, agreed.
A price is in the reference data, its value transferred to a
transaction is no
I mean something else. You have a reference data set which is accessed
to get the current value of reference elements and store transactions to
record events. The transaction trails provide event history.
A price is in the reference data, its value transferred to a transaction
is no longer a
Hi John,
A general rule of database design is to seperate reference and
transactional data. Then you can have a normalized database in a
dynamic environment.
Yes, I think that's what I am designing.
The reference data is the products table (and potentially customer
table etc)
The
Hi Puneet,
ok, your problem makes a bit more sense
Great :-)
but still, much in it doesn't make sense.
OK, I'll see if I can clarify further.
even if the products table is changing, the buy, sell, and
description of a given item shouldn't change.
The buy and sell price of products
A general rule of database design is to seperate reference and
transactional data. Then you can have a normalized database in a
dynamic environment.
T wrote:
Hi Puneet and John,
You each respectively said:
Why are you repeating the Code, Buy, Sell, and Description columns in
the
ok, your problem makes a bit more sense, but still, much in it doesn't
make sense.
On 6/18/07, T <[EMAIL PROTECTED]> wrote:
Hi Puneet and John,
You each respectively said:
> Why are you repeating the Code, Buy, Sell, and Description columns
> in the Sale_Products table when they already
Hi Puneet and John,
You each respectively said:
Why are you repeating the Code, Buy, Sell, and Description columns
in the Sale_Products table when they already exists in the Products
table?
A traditional goal in database design is to place data in "Third
Normal Form" which means in
On 6/17/07, T <[EMAIL PROTECTED]> wrote:
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
A traditional goal in database design is to place data in "Third Normal
Form" which means in essence that each data element is only stored in
one place. Acesses join the rows to deliver data.
A normalized database does not hold redundant data and changing the
value of one element changes its
Hi John,
Thanks for your reply.
You would have a better database if you normalize it and not do
what you propose.
Meaning what, exactly?
Obviously, I've only given rough indications as to my data source,
such as:
the huge Products table (which is actually a UNION ALL of various
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
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,
19 matches
Mail list logo
