agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@lists.postgresql.org
Subject: Sv: More optimized SQL
Date: Thu, 4 Jan 2018 14:27:20 +0100 (CET)
Message-ID: <VisenaEmail.3d.173996b7c8f4be9c.160c159b99c@tc7-visena> (raw)
In-Reply-To: <000001d38553$6c638e80$452aab80$@1nar.com.tr>
På torsdag 04. januar 2018 kl. 12:59:06, skrev Ertan Küçükoğlu <
ertan.kucukoglu@1nar.com.tr <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
Message-ID: <VisenaEmail.3d.173996b7c8f4be9c.160c159b99c@tc7-visena>
Permalink: ../VisenaEmail.3d.173996b7c8f4be9c.160c159b99c@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.3d.173996b7c8f4be9c.160c159b99c@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com, pgsql-sql@lists.postgresql.org
Subject: Re: Sv: More optimized SQL
In-Reply-To: <VisenaEmail.3d.173996b7c8f4be9c.160c159b99c@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox