Reputation: 93
I have a table:
CREATE TABLE sale_tender_t0
(
sten_bill bigint NOT NULL,
sten_paymentform smallint NOT NULL,
sten_tenderamount integer,
sten_changeamount integer,
sten_isfiscal character(1),
sten_accid integer,
sten_id bigserial NOT NULL,
sten_map_user integer,
sten_storeid integer NOT NULL
and some data, like:
11246276310734;0;5000;2748;"0";;1913;;1
11246277847093;0;25000;2083;"0";;1914;;1
11246280935156;0;2254;0;"0";;1915;;1
11246281048531;0;2240;100;"0";;1916;;1
11246281345250;0;10000;1360;"0";;1917;;1
sten_bill
- should have unique data, but sometimes happens the data sten_bill is duplicated.
using this script i can find duplicated data:
select sten_bill from sale_tender_t0 GROUP by sten_bill
HAVING count(*)>1;
But i need to fix the table, for example:
11246276310734;0;5000;2748;"0";;1913;;1
11246276310734;1;5000;2748;"0";;1913;;1
should fix like:
11246276310734;1;10000;5496;"0";;1913;;1
Delete first column, and change second column data column2.sten_tenderamount + column1.sten_tenderamount
and column2.sten_changeamount + column2.sten_changeamount
Upvotes: 0
Views: 30
Reputation: 8105
WITH max_id_and_summed_amounts AS (
SELECT
sten_bill,
SUM(sten_tenderamount) AS sten_tenderamount,
SUM(sten_changeamount) AS sten_changeamount,
MAX(sten_id) AS sten_id
FROM
sale_tender_t0
GROUP BY
sten_bill
HAVING
COUNT(*)>1
),
newest_line AS (
SELECT
miasa.sten_bill,
stt.sten_paymentform,
miasa.sten_tenderamount,
miasa.sten_changeamount,
stt.sten_isfiscal,
stt.sten_accid,
miasa.sten_id,
stt.sten_map_user,
stt.sten_storeid
FROM
max_id_and_summed_amounts miasa INNER JOIN sale_tender_t0 stt
ON
miasa.sten_id=stt.sten_id
),
deleted_rows AS (
DELETE FROM
sale_tender_t0
WHERE
sten_bill IN (
SELECT
sten_bill
FROM
max_id_and_summed_amounts
)
)
INSERT INTO
sale_tender_t0
SELECT
*
FROM
newest_line;
Edit: PostgreSQL versions 8.4 and older do not support Common Table Expressions, so you need to use temporary tables instead.
CREATE TEMPORARY TABLE max_id_and_summed_amounts
AS
SELECT
sten_bill,
SUM(sten_tenderamount) AS sten_tenderamount,
SUM(sten_changeamount) AS sten_changeamount,
MAX(sten_id) AS sten_id
FROM
sale_tender_t0
GROUP BY
sten_bill
HAVING
COUNT(*)>1;
CREATE TEMPORARY TABLE newest_line
AS
SELECT
miasa.sten_bill,
stt.sten_paymentform,
miasa.sten_tenderamount,
miasa.sten_changeamount,
stt.sten_isfiscal,
stt.sten_accid,
miasa.sten_id,
stt.sten_map_user,
stt.sten_storeid
FROM
max_id_and_summed_amounts miasa INNER JOIN sale_tender_t0 stt
ON
miasa.sten_id=stt.sten_id;
DELETE FROM
sale_tender_t0
WHERE
sten_bill IN (
SELECT
sten_bill
FROM
max_id_and_summed_amounts
);
INSERT INTO
sale_tender_t0
SELECT
*
FROM
newest_line;
DROP TABLE max_id_and_summed_amounts;
DROP TABLE newest_line;
Upvotes: 2