Обсуждение: More optimized SQL
På torsdag 04. januar 2018 kl. 12:59:06, skrev Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>:
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)
Yes, use UPDATE <table> SET ... FROM <...> WHERE ...
https://www.postgresql.org/docs/10/static/sql-update.html
which is a PostgreSQL-extention to the SQL-standard.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963