Flakheart wrote:
> I need to split a table into two as the current monolithic structure is
> incredibly wasteful but don't know how to do such a possibly complicated
> thing. I remember an analyser available years ago for MS Access database
> that did this but none found for SQLite.
>
> My bloated table (Shoppinghistory) consists of the fields below, all of
> which are text except for 'record' which is an autoincrement field.
>
> Creating tables is no problem (Grin).
>
> Old table New item table New history table
> (ShoppingHistory) (ItemData) (ItemHistory)
> item Itemname ItemDataId (Points to
> Record in ItemData)
> category Category
> brand Brandname
> manufacturer Manufacturer
> boughtfrom Packaging
> boughton Boughton
> quantity Quantity
> units Units
> weight Weight
> aisle Aisle
> price Price
> discount Discount
> total Total
> note Note
> picture Picture
> barcode Barcode
> deleted Deleted
> record Record Record
>
> For each item in the original table (Shoppinghistory) that matches the new
> (ItemData) table on the "ItemName,Category,BrandName,Manufacturer" fields,
I am assuming the "Record" columns are declared as INTEGER PRIMARY KEY
so that they are autoincrementing.
CREATE UNIQUE INDEX IName_Cat_BName_Manu ON
ItemData(ItemName,Category,BrandName,Manufacturer);
INSERT OR IGNORE INTO ItemData(
Itemname, Category, Brandname, Manufacturer, Packaging, Units, Weight,
Note, Picture, Barcode, Deleted)
SELECT item, brand, manufacturer, boughtfrom, units, weight,
note, picture, barcode, deleted
FROM ShoppingHistory;
The "OR IGNORE" makes SQLite siltently ignore any records that would
violate the UNIQUE index.
> the "boughton,quantity,aisle,price,discount,total data needs to be copied to
> the (ItemHistory) table with the (ItemDataId) column in the (ItemHistory)
> table containing the record number of the (ItemData) record it matched.
The ItemData ID can be looked up with a correlated subquery:
INSERT INTO ItemHistory(
Boughton, Quantity, Aisle, Price, Discount, Total, ItemDataId)
SELECT boughton, quantity, aisle, price, discount, total,
(SELECT Record
FROM ItemData
WHERE Itemname = ShoppingHistory.item
AND Category = ShoppingHistory.category
AND Brandname = ShoppingHistory.brand
AND Manufacturer = ShoppingHistory.manufacturer)
FROM ShoppingHistory;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
