MIME-Version: 1.0
From: Jamie Thompson <jthompson.neuk@gmail.com>
Date: Fri, 22 Mar 2024 11:36:54 +0000
Message-ID: 
 <CABoe=cS68ZKuY09AUE92QuZbZG=UQozBL0m+nWQQ-oLFfDhfFA@mail.gmail.com>
Subject: UPDATE with AND clarification
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003d23e106143e3ebf"
Archived-At: 
 <https://www.postgresql.org/message-id/CABoe%3DcS68ZKuY09AUE92QuZbZG%3DUQozBL0m%2BnWQQ-oLFfDhfFA%40mail.gmail.com>
Precedence: bulk

--0000000000003d23e106143e3ebf
Content-Type: text/plain; charset="UTF-8"

Hi,

Struggled to find a section about this when scanning through the docs, but
awkward keywords to scan for, so apologies for duplicating a request that
has very likely come through before.

I'm aware a correct syntax for a multi column UPDATE is:
> UPDATE tablename SET col1 = val1, col2 = val2 WHERE id = X

Earlier today, I was running some sql for someone which they had submitted
as:
> UPDATE tablename SET col1 = val1 AND col2 = val2 WHERE id = X

This returned UPDATE 1, but the row hadn't actually changed, col1 was still
the old value.

Please could you explain what is actually happening here? Is there a
section in the docs about this?
If I EXPLAIN (ANALYZE, VERBOSE) the update with the AND syntax, I can see
the top level update node has 0 rows:
> Update on public.tablename (cost=11.91..24.87 rows=0 width=0) (actual
time=1.377..1.378 rows=0 loops=1)
So why is it returning:
> UPDATE 1

Is it because it's only looking at col2 and in this scenario that value was
already correct? What's it doing with the first column (col1) in the SET?

Thanks, hoping this can be clarified so I can help others avoid the mistake
in future.

--0000000000003d23e106143e3ebf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div><div><div><div><div><div>Hi,<br><br></=
div>Struggled to find a section about this when scanning through the docs, =
but awkward keywords to scan for, so apologies for duplicating a request th=
at has very likely come through before.<br><br></div>I&#39;m aware a correc=
t syntax for a multi column UPDATE is:<br></div>&gt; UPDATE tablename SET c=
ol1 =3D val1, col2 =3D val2 WHERE id =3D X<br><br></div>Earlier today, I wa=
s running some sql for someone which they had submitted as:<br>&gt; UPDATE=
=C2=A0tablename SET col1 =3D val1 AND col2 =3D val2 WHERE id =3D X<br><br><=
/div>This returned UPDATE 1, but the row hadn&#39;t actually changed, col1 =
was still the old value.<br><br></div>Please could you explain what is actu=
ally happening here? Is there a section in the docs about this?<br></div>If=
 I EXPLAIN (ANALYZE, VERBOSE) the update with the AND syntax, I can see the=
 top level update node has 0 rows:<br>&gt; Update on public.tablename (cost=
=3D11.91..24.87 rows=3D0 width=3D0) (actual time=3D1.377..1.378 rows=3D0 lo=
ops=3D1)<br></div>So why is it returning:<br> &gt; UPDATE 1<br><br></div><d=
iv>Is it because it&#39;s only looking at col2 and in this scenario that va=
lue was already correct? What&#39;s it doing with the first column (col1) i=
n the SET?<br><br></div>Thanks, hoping this can be clarified so I can help =
others avoid the mistake in future.<br></div>

--0000000000003d23e106143e3ebf--