I need a stored procedure (in PLPgsql or SQL) or a pure SQL statement.
I have the table seen below and I need someone to go through all the entries in the table and on duplicates set the "deleted" column to "true" and add the following text to the beginning of the "text" column: duplicate of receipt with id: X. Please only mark the receipt with the highest id as duplicate and do not change anything on the duplicate with the lowest id.
Please provide one single sql statement, which will create the tables, drop the old stored procedure, create the new stored procedure, add sample data to the table and show output from your stored procedure. Your sample data should cover all special cases so I will not need to make any additional tests.
DROP TABLE IF EXISTS receipts;
CREATE TABLE receipts (
id SERIAL PRIMARY KEY,
amount NUMERIC(20, 2) DEFAULT 0.00,
amount_original_currency NUMERIC(20, 2) DEFAULT 0.00,
currency currency_enum DEFAULT 'DKK', -- Use USD for dollar, DKK for danish kroner etc.
vat_code VARCHAR(10), -- Used to record the VAT code of the company our customer has bought from or sold to.
deleted BOOLEAN DEFAULT false
Receipts are duplicate if the following are equal:
- either amount or amount_original_currency
- vat_code (or if one or both of these are blank or null)
I will pay 15$ for this, and you must deliver within 2 days. Please provide your [url removed, login to view] username in your bid.