-4

The queries of this question are here: http://sqlfiddle.com/#!17/16abc/5

I have this table person which has two columns, id and age. When I run an update statement

update person
set age = ((select person.age) + 1)
where person.id = 1;

The age is set using the subquery((select person.age) + 1.

What are the constraints which are applied on this select expression? Does it inherit the where clause of the update statement, retrieving the age of the row with person.id = 1?

From testing against some data it does appear that this is the case.

In this specific case the age can be set directly without resorting to a sub-select, but I was looking at this answer https://stackoverflow.com/a/6535089/1089912 and wondered if these select expressions when used in an update statement, inherit the constraints of the surrounding update statement.

abhijat
  • 535
  • 6
  • 12

1 Answers1

1

Your "subquery" (select person.age) is identical to the column reference person.age or just age without the select because it does not have a from clause.

You can see the effect of the "subquery", when you use it in a SELECT statement's column list:

select id, (select person.age) as age
from person
where id = 1;

So your UPDATE is 100% identical to:

update person
  set age = age + 1
where id = 1;
  • Is this a general rule, that these expressions without a where clause are treated the same as column references? When I wrote the question I was trying to provide a minimal working example for a more complex scenario, such as `SET names = array(SELECT unnest(names) EXCEPT ... )` related to arrayfields in postgres. – abhijat Sep 12 '20 at 09:14
  • They are only treated the same if the SELECT is a scalar query, i.e. returns a single value –  Sep 12 '20 at 09:20