DEFINITIONS

Definitions More Info.
Definition ID12.078
TitlePOSTGRESQL
CategoryNOTES
Definitionwhile ile zor update yontem
Definition Description/*
DROP TABLE "TransactionUpdateTmp"
CREATE TABLE IF NOT EXISTS public."TransactionUpdateTmp"
(
"TmpId" bigserial,
"TransactionId" bigint,
"ProcessTime" timestamp without time zone,
"IsUpdated" integer
)
ALTER TABLE IF EXISTS public."TransactionUpdateTmp"
ADD PRIMARY KEY ("TmpId");

--TRUNCATE TABLE "TransactionUpdateTmp"
INSERT INTO public."TransactionUpdateTmp" ("TransactionId","ProcessTime","IsUpdated")
SELECT lt."TransactionId",lt."ProcessTime", 0
FROM "Transactions" t
INNER JOIN "LastTransactions" lt ON t."Id" = lt."TransactionId"
AND lt."TransactionId" >=100000000
AND lt."TransactionId" < 110000000
WHERE t."LastProcessTime" IS NULL;

CREATE INDEX IF NOT EXISTS "IX_TransactionUpdateTmp_TransactionId"
ON public."TransactionUpdateTmp" USING btree
("TransactionId","ProcessTime" )
TABLESPACE "indexTableSpace";

CREATE INDEX IF NOT EXISTS "IX_TransactionUpdateTmp_ProcessTime"
ON public."TransactionUpdateTmp" USING btree
("ProcessTime","TransactionId")
TABLESPACE "indexTableSpace";

CREATE INDEX IF NOT EXISTS "IX_TransactionUpdateTmp_IsUpdated"
ON public."TransactionUpdateTmp" USING btree
("IsUpdated")
TABLESPACE "indexTableSpace";

SELECT COUNT(1) FROM "TransactionUpdateTmp"
*/
DO $$
DECLARE i integer;
DECLARE j integer;
BEGIN
i:=(SELECT MIN("TmpId")FROM "TransactionUpdateTmp" WHERE "IsUpdated"=0);
j:=(SELECT MAX("TmpId") FROM "TransactionUpdateTmp" WHERE "IsUpdated"=0);
WHILE(i<=j)
loop
--raise notice 'Update Edildi: %', i;
UPDATE "Transactions" t
SET "LastProcessTime" = lt."ProcessTime"
FROM "TransactionUpdateTmp" lt
WHERE t."Id" = lt."TransactionId"
AND lt."TmpId"=i
AND lt."IsUpdated"=0;

UPDATE "TransactionUpdateTmp"
SET "IsUpdated"=1
WHERE "TmpId"=i
AND "IsUpdated"=0;
i = i+1;
COMMIT;
end loop;
END
$$;
RecordBycunay
Record Date09-06-2022 18:00:11
Düzenle
Kopyala
Sil