CODEWITHSUNDEEP
sqlitesql-updatecorrelated-subquery
Zeks
Zeks

Reputation: 2385

How to set all values for a column to a max value on a certain WHERE?

If I have:

 2 baskets of oranges with 7 and 10 each

 3 baskets of peaches with 12 and 15 each

then I want to set: 

for every orange basket value of maxfruit to 10 and 

for every peach basket value of maxfruit to 15

I tried 

 update baskets set maxfruit = (select max(fruitCount) from baskets b where b.fruit = fruit)

but it just sets everything to 15...

Upvotes: 0

Views: 580

Answers (2)

CL.
CL.

Reputation: 180192

In SQL, when you are referencing a column by its name, the table instance that you end up with is the innermost one, unless you use a table prefix.

So fruit refers to the innermost instance, b. This means that b.fruit and fruit are always the same value.

To refer to the outer table instance, you must use the name of the outer table:

update baskets
set maxfruit = (select max(fruitCount)
                from baskets b
                where b.fruit = baskets.fruit);
                                ^^^^^^^^

(And instead of b.fruit, you could write just fruit, but that could be unclear.)

Upvotes: 2

Moffatt
Moffatt

Reputation: 39

your update is just pulling the max from the whole table you can use a sub query to pull out the max for each fruit

UPDATE b 
SET b.maxfruit = b2.fruitCount 
FROM baskets b 
INNER JOIN (SELECT fruit, MAX(fruitCount) AS fruitCount
            FROM baskets
            GROUP BY fruit) b2 ON b.fruit = b2.fruit

Upvotes: -1

Related Questions