Update a row based on the contents of another row
Question
We want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.Schema reminder ▼
Your Answer
Answers and Discussion Show
update cd.facilities facs
set
membercost = (select membercost * 1.1 from cd.facilities where facid = 0),
guestcost = (select guestcost * 1.1 from cd.facilities where facid = 0)
where facs.facid = 1;
Updating columns based on calculated data is not too intrinsically difficult: we can do so pretty easily using subqueries. You can see this approach in our selected answer.
As the number of columns we want to update increases, standard SQL can start to get pretty awkward: you don't want to be specifying a separate subquery for each of 15 different column updates. Postgres provides a nonstandard extension to SQL called UPDATE...FROM that addresses this: it allows you to supply a FROM clause to generate values for use in the SET clause. Example below:
update cd.facilities facs
set
membercost = facs2.membercost * 1.1,
guestcost = facs2.guestcost * 1.1
from (select * from cd.facilities where facid = 0) facs2
where facs.facid = 1;