I came across the following exercise and spent some time figuring out the best approach, which is a very specific challenge. I hope the logic might be applicable for a use case where you need to dynamically update a column based on values present in other fields.
As shown in the visual below, the Main goal of this exercise is to populate an empty results field based on two existing fields start_bottles and take_down.
Here are the steps I took to tackle this exercise:
Step 1: Reference the Song
My first goal was to create a Common Table Expression (CTE).
A CTE is a temporary, table that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps organise complex queries by breaking them down into simpler, readable, and reusable blocks.
I created a CTE where each row corresponded to a single verse of the song, tagged with its respective bottle number. This then allowed me to easily reference the individual verses later on.
Step 2: Figure out the Logic
Next, I tried to wrap my head around the logic needed to retrieve the correct verses based on the starting bottle and the number of bottles taken down. The query needed to return the exact number of concatenated verses required for each record in the original table. I used the following subquery to achieve this:
SELECT group_concat(lyrics, '
')
FROM (
SELECT lyrics, NumberofBottle
FROM Verses
ORDER BY NumberofBottle DESC
)
WHERE NumberofBottle BETWEEN (start_bottles - take_down) + 1 AND start_bottles
);
You may notice "+1" within the WHERE clause; I added this because the BETWEEN keyword is inclusive and without itm the query would return an extra verse. For example. If your Start_bottles = 10 and your Take_down value was 3. The BETWEEN clause (without the "+1") would return verses [7,8,9,10]. Not [8,9,10].
Step 3 : Updating the table
Finally, I placed this subquery into an UPDATE statement (as shown below). This would then populate the empty result field.
UPDATE "bottle-song"
SET result = (
SELECT group_concat(lyrics, '
')
FROM (
SELECT lyrics, NumberofBottle
FROM Verses
ORDER BY NumberofBottle DESC
)
WHERE NumberofBottle BETWEEN (start_bottles - take_down) + 1 AND start_bottles
);
If you are curious about both the challenge (you can also find similar challenges on this website) and my full solution.
I have linked both the exercise and my solution on git hub below :
Exercise :
My Solution :
arushipant-spec