SQLize | PHPize | SQLtest

CREATE TABLE FeedPurchase ( purchase_id SERIAL PRIMARY KEY, supplier_id INT NOT NULL, purchase_date DATE NOT NULL, quantity_kg DECIMAL(6,2) NOT NULL, cost DECIMAL(10,2) NOT NULL, feed_type VARCHAR(50) ); CREATE TABLE Supplier ( supplier_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, contact_person VARCHAR(100), phone_number VARCHAR(20), email VARCHAR(100), address TEXT, rating DECIMAL(3,2), delivery_time_days INT ); WITH LatestPurchases AS ( SELECT supplier_id, MAX(purchase_date) AS latest_date FROM FeedPurchase GROUP BY supplier_id ) MERGE INTO FeedPurchase AS target USING ( SELECT s.supplier_id, CURRENT_DATE AS purchase_date, 100.00 AS quantity_kg, 500.00 AS cost, 'Corn' AS feed_type FROM Supplier s JOIN LatestPurchases lp ON s.supplier_id = lp.supplier_id ) AS src ON target.supplier_id = src.supplier_id AND target.purchase_date = src.purchase_date WHEN MATCHED THEN UPDATE SET cost = src.cost WHEN NOT MATCHED THEN INSERT (supplier_id, purchase_date, quantity_kg, cost, feed_type) VALUES (src.supplier_id, src.purchase_date, src.quantity_kg, src.cost, src.feed_type);