Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eX4B4-0005bh-1c for pgsql-sql@arkaria.postgresql.org; Thu, 04 Jan 2018 11:59:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eX4B3-0000tK-IA for pgsql-sql@arkaria.postgresql.org; Thu, 04 Jan 2018 11:59:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eX4B3-0000tA-CK for pgsql-sql@lists.postgresql.org; Thu, 04 Jan 2018 11:59:17 +0000 Received: from mta04.2.doruk.net.tr ([81.21.172.141] helo=mta04.doruk.net.tr) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eX4Az-0001mj-QV for pgsql-sql@postgresql.org; Thu, 04 Jan 2018 11:59:16 +0000 Received: from cmail19.webkontrol.doruk.net.tr ([212.58.6.99]) by mta04.doruk.net.tr with esmtp (Exim 4.86) (envelope-from ) id 1eX489-0005BN-Hy for pgsql-sql@postgresql.org; Thu, 04 Jan 2018 14:56:17 +0300 Received: from DESKTOPM80CG12 ([94.122.19.189]) by cmail19.webkontrol.doruk.net.tr (IceWarp 11.1.2.0) with ASMTP id 201801041459324525 for ; Thu, 04 Jan 2018 14:59:32 +0300 From: =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= To: Subject: More optimized SQL Date: Thu, 4 Jan 2018 14:59:06 +0300 Message-ID: <000001d38553$6c638e80$452aab80$@1nar.com.tr> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdOFUndj5Dq0oZTfRC6hXwLnaBAysQ== Content-Language: tr List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 =3D (select verilencevap from cevaplar where cevaplar.yil =3D ekstreler.yil and cevaplar.ay =3D ekstreler.ay and cevaplar.turu =3D ekstreler.turu and cevaplar.emailid =3D ekstreler.emailid order by = cevapzamani desc limit 1), soncevapzamani =3D (select cevapzamani from cevaplar where = cevaplar.yil =3D ekstreler.yil and cevaplar.ay =3D ekstreler.ay and cevaplar.turu =3D ekstreler.turu and cevaplar.emailid =3D ekstreler.emailid order by = cevapzamani desc limit 1) where emailgonderilecek =3D false and soncevapzamani <> (select cevapzamani from cevaplar where cevaplar.yil = =3D ekstreler.yil and cevaplar.ay =3D ekstreler.ay and cevaplar.turu =3D ekstreler.turu and cevaplar.emailid =3D ekstreler.emailid order by = cevapzamani desc limit 1) Thanks & regards, Ertan K=FC=E7=FCko=F0lu=20