jeudi 25 décembre 2014

Sqlite Insert or Replace always adding new row not replacing

Here are my tables and query. I always end up inserting new row but not updating/replacing current data. I hope you guys can figure out what I have done wrong.



CREATE TABLE [imf_products] (
[imfprod_id] integer NOT NULL
, [imf_id] int NOT NULL
, [company_id] int NOT NULL
, [product_id] int NOT NULL
, [color_id] int NOT NULL
, [size_id] int NOT NULL
, [imf_type] nvarchar(100) NOT NULL
, [date] datetime NOT NULL
, price nvarchar(100) not null
, [comment] nvarchar(100) NULL
, quantity int not null
, CONSTRAINT [sqlite_master_PK_imfprod] PRIMARY KEY ([imfprod_id])
);


CREATE TABLE [stocks] (
[stocks_id] integer NOT NULL
, [product_id] int NOT NULL
, [color_id] int NULL
, [size_id] int NULL
, [price] int NULL
, [quantity] int NULL
, [status] nvarchar(100) NULL
, CONSTRAINT [sqlite_master_PK_product] PRIMARY KEY ([stocks_id])
);

CREATE TRIGGER add_stock9 AFTER INSERT ON imf_products
FOR EACH ROW
when new.imf_type='Inv-IN'
BEGIN
INSERT OR REPLACE INTO stocks (color_id, size_id, price, quantity, product_id) values (new.color_id, new.size_id,
new.price, new.quantity,(select * from stocks where product_id=new.product_id));
END;

insert into imf_products (imf_id, company_id, product_id, color_id, size_id, imf_type, date, price, quantity) values
(2,2,9,3,4,'Inv-IN',('now'),100,6);


Also If you can help me add old quantity with new one.


Aucun commentaire:

Enregistrer un commentaire