Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1saz9j-00Cot2-Ts for pgsql-general@arkaria.postgresql.org; Mon, 05 Aug 2024 14:57:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1saz9i-00EKql-3M for pgsql-general@arkaria.postgresql.org; Mon, 05 Aug 2024 14:57:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1saz9h-00EKqd-MX for pgsql-general@lists.postgresql.org; Mon, 05 Aug 2024 14:57:49 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1saz9e-003EcH-NL for pgsql-general@lists.postgresql.org; Mon, 05 Aug 2024 14:57:48 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-264a12e05b9so6154401fac.1 for ; Mon, 05 Aug 2024 07:57:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1722869866; x=1723474666; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=/UyGpnSycSdgdtnPuUdNGtYB2y6u48+7ODV4l1eJhgc=; b=jzkpOBV2vM1L+nuzDc/MQlE5f9Z58W4VeJV9Sz0Fgcdh2CJM5H9Y5MyK4ARxXGkn9E l4cVAcz0GdaIEM+GpAoslrPg26m5aN5mOnuLmzACdzYu3W3B4i+WDnIUbTOVXbgHYYfT pB5ltvZ8XTDWe46aYb8PizfBmEEorR4cooNAsdOSGt1zJ3y7jVSd/6Ft3cnuotEeQVYy u9MKxdsrTiA90HRqIHqzq1bVbSDFccMDnlbvEC7dTR06F1gsFCeR8QTR6XatDhYCT46/ MXIviRRaberfb39mF9wCSV328JfAkqb1v2Us4u38QB3i1NueaSQGByc/Y6G3t4H3g/rX 1P9w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1722869866; x=1723474666; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=/UyGpnSycSdgdtnPuUdNGtYB2y6u48+7ODV4l1eJhgc=; b=DzV4ovjxWPZ4JTuRZfzWbbzaETAHzTymf2hrUJnn+KuiGfVeJvNQD2eItEBaot8qmg 2/hb2eyoS06imhbNKmCDDtm2P/jZ3QQPmhafIo9hQPFI7sEJ/bRBsw84z1WY3Odzdfd9 0mRaiAdYTUxjnIYwDp+Tb+SE0EPrZcCFPPhIRKxPXJ2P37wKOgazfV/gLD20JfY1cUNJ 9Z3wySeeDnCfopn2Jgwcmp2mnSTWor/GZ6xGlfPf1fcjNRK6UJQibDfBdKC7xBoW4NQj iueK8esean2KiyAm3GhMJNpEIi5kZxstKusZdYAaxDvbcJWzsT7bQnaQi0Tqy+hcq8hc b6pA== X-Forwarded-Encrypted: i=1; AJvYcCWZ/Rnd+KJmRjkBSuT/YJxKa5plmBLjPXx3BVtc12AMRUVMY5opjL9YKGjAWrKi2UFm8QqsMSb8gBrv/tzy@lists.postgresql.org X-Gm-Message-State: AOJu0YwIOLyVmNi1I6aedGwChg1pkK3987slKK/eUxBK753bmoCfz/bG wDORBQxjHFkO31K7skXWoI7FIxGaTN4z5YDwaEsHSk7GvsSd42TM5oeDeJ0+ICCpnqVBSlGCA6d uAqivglw3nW760b9+U9OEqlKrQOA= X-Google-Smtp-Source: AGHT+IFCU/uWQPPWmt5IwvIFKT4pr2/+pu5lgYy0mXG/BcUsK0o6H+Mvu68X2kHI6u3sNttL5qWr8WsiI8ZM32EQpq0= X-Received: by 2002:a05:6870:304f:b0:260:fcca:cf8e with SMTP id 586e51a60fabf-26891aa6770mr14306658fac.7.1722869865786; Mon, 05 Aug 2024 07:57:45 -0700 (PDT) MIME-Version: 1.0 References: <1064261.1722866217@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Mon, 5 Aug 2024 07:57:08 -0700 Message-ID: Subject: Re: UPDATE-FROM and INNER-JOIN To: Dominique Devienne Cc: Tom Lane , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000005837ce061ef0e65f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005837ce061ef0e65f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Aug 5, 2024 at 7:36=E2=80=AFAM Dominique Devienne wrote: > I'd rather SQLite and PostgreSQL continue to agree on this, > but not in a restrictive way. I.e., you want to support the SQL Server syntax; allow the table named in UPDATE to be repeated, without an alias, in which case it is taken to represent the table being updated. And then allow the usual FROM clause to take form. Personally I get around this by simply doing: UPDATE tbl FROM (...) AS to_update WHERE tbl.id =3D to_update.id A bit more verbose in the typical case but the subquery in FROM can be separately executed during development then just plugged in. There is no noise in the outer where clause since its only purpose is to join the subquery to the table to be updated. The subquery has the full separation of filters from joins that one would like to have. David J. --0000000000005837ce061ef0e65f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Aug 5, 2024 at 7:36=E2=80=AFAM Dominique Devienne = <ddevienne@gmail.com> wrot= e:
I'd rather SQLite and PostgreSQL continue to= agree on this,
but not in a restrictive way.

I.e., you = want to support the SQL Server syntax; allow the table named in UPDATE to b= e repeated, without an alias, in which case it is taken to represent the ta= ble being updated.=C2=A0 And then allow the usual FROM clause to take form.=

Personally I get around this by simply doing:

UPDATE tbl
FROM (...) AS to_update

A bit more verbose in the typical cas= e but the subquery in FROM can be separately executed during development th= en just plugged in.=C2=A0 There is no noise in the outer where clause since= its only purpose is to join the subquery to the table to be updated.=C2=A0= The subquery has the full separation of filters from joins that one would = like to have.

David J.

--0000000000005837ce061ef0e65f--