public inbox for [email protected]help / color / mirror / Atom feed
UPDATE with AND clarification 3+ messages / 2 participants [nested] [flat]
* UPDATE with AND clarification @ 2024-03-22 11:36 Jamie Thompson <[email protected]> 2024-03-22 13:35 ` Re: UPDATE with AND clarification David G. Johnston <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Jamie Thompson @ 2024-03-22 11:36 UTC (permalink / raw) To: [email protected] 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE with AND clarification 2024-03-22 11:36 UPDATE with AND clarification Jamie Thompson <[email protected]> @ 2024-03-22 13:35 ` David G. Johnston <[email protected]> 2024-03-22 19:51 ` Re: UPDATE with AND clarification Jamie Thompson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David G. Johnston @ 2024-03-22 13:35 UTC (permalink / raw) To: Jamie Thompson <[email protected]>; +Cc: [email protected] <[email protected]> On Friday, March 22, 2024, Jamie Thompson <[email protected]> wrote: > > 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 > I presume these columns are booleans. So you wrote something like: Set col1 = ( true and false = true ) Which in this case must coincidentally have produced whatever boolean value already existed in col1. David J. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE with AND clarification 2024-03-22 11:36 UPDATE with AND clarification Jamie Thompson <[email protected]> 2024-03-22 13:35 ` Re: UPDATE with AND clarification David G. Johnston <[email protected]> @ 2024-03-22 19:51 ` Jamie Thompson <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Jamie Thompson @ 2024-03-22 19:51 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: [email protected] Yes they were both boolean columns. It felt odd at the time, but yes it makes sense. Thank you. On Fri, 22 Mar 2024, 13:35 David G. Johnston, <[email protected]> wrote: > On Friday, March 22, 2024, Jamie Thompson <[email protected]> > wrote: > >> >> 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 >> > > I presume these columns are booleans. So you wrote something like: > Set col1 = ( true and false = true ) > > Which in this case must coincidentally have produced whatever boolean > value already existed in col1. > > David J. > > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-22 19:51 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-03-22 11:36 UPDATE with AND clarification Jamie Thompson <[email protected]> 2024-03-22 13:35 ` David G. Johnston <[email protected]> 2024-03-22 19:51 ` Jamie Thompson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox