From: | Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | More optimized SQL |
Date: | 2018-01-04 11:59:06 |
Message-ID: | 000001d38553$6c638e80$452aab80$@1nar.com.tr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello,
I am using PostgreSQL 10.1 64bit on Windows x64. There are following table
definitions in my database:
Table "public.ekstreler"
Column | Type |
Modifiers
------------------------+--------------------------------+------------------
-------------------------------------------
autoinc | integer | not null default
nextval('ekstreler_autoinc_seq'::regclass)
yil | smallint |
ay | smallint |
turu | smallint |
firmakodu | character varying(20) |
carikodu | character varying(40) |
parabirimi | character varying(5) |
borc | numeric(20,4) |
alacak | numeric(20,4) |
borcbakiye | numeric(20,4) |
alacakbakiye | numeric(20,4) |
emailid | uuid |
emailgonderilecek | boolean |
emaildenemesi | smallint | default 0
emailsondenemezamani | timestamp(0) without time zone |
emailsonhatasi | text |
emailgonderilenadet | smallint | default 0
emailsongonderimzamani | timestamp(0) without time zone |
cevapadedi | smallint | default 0
soncevap | boolean |
soncevapzamani | timestamp(0) without time zone |
kaydeden | character varying(20) |
kayitzamani | timestamp(0) without time zone |
degistiren | character varying(20) |
degisiklikzamani | timestamp(0) without time zone |
Indexes:
"ekstreler_pkey" PRIMARY KEY, btree (autoinc)
Table "public.cevaplar"
Column | Type |
Modifiers
----------------------+--------------------------------+--------------------
----------------------------------------
autoinc | integer | not null default
nextval('cevaplar_autoinc_seq'::regclass)
yil | smallint |
ay | smallint |
turu | smallint |
emailid | uuid |
verilencevap | boolean |
cevapzamani | timestamp(0) without time zone |
cevapverenipnumarasi | cidr |
kaydeden | character varying(20) |
kayitzamani | timestamp(0) without time zone |
Indexes:
"cevaplar_pkey" PRIMARY KEY, btree (autoinc)
Cevaplar table is getting data from an outside source and an application is
inserting each record into that table. After import operation completes, it
is needed to update with most recent data that is different than in
ekstreler table. Below is current SQL that I came up with. However, I wonder
if there is a better approach to write it.
update ekstreler
set
soncevap = (select verilencevap from cevaplar where cevaplar.yil =
ekstreler.yil and cevaplar.ay = ekstreler.ay and cevaplar.turu =
ekstreler.turu and cevaplar.emailid = ekstreler.emailid order by cevapzamani
desc limit 1),
soncevapzamani = (select cevapzamani from cevaplar where cevaplar.yil =
ekstreler.yil and cevaplar.ay = ekstreler.ay and cevaplar.turu =
ekstreler.turu and cevaplar.emailid = ekstreler.emailid order by cevapzamani
desc limit 1)
where
emailgonderilecek = false and
soncevapzamani <> (select cevapzamani from cevaplar where cevaplar.yil =
ekstreler.yil and cevaplar.ay = ekstreler.ay and cevaplar.turu =
ekstreler.turu and cevaplar.emailid = ekstreler.emailid order by cevapzamani
desc limit 1)
Thanks & regards,
Ertan Küçükoğlu
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2018-01-04 13:27:20 | Sv: More optimized SQL |
Previous Message | Francis Santiago | 2017-12-29 21:48:32 | Re: Oacle to Postgres migration |