SQLite Forum

Update database item
Login

Update database item

(1) By Birdy (SQLBirdy) on 2021-02-21 12:02:51 [link] [source]

Hello,
I am using Windows and SQLite Expert.
I have a SQLite database with items like Title, Artist, Ranking, Points, HP (highest position), WK (number of weeks) and some other items from the dutch Top40 Chart. Every week the new list is published on internet and I add the new list to the database.

After inserting the new list I reorder the database with
SELECT Ranking, Points, HP, Wk, Artist, Title 
FROM   Top40
ORDER  BY Points DESC, Hp ASC, Wk DESC

The result looks like this:
Ranking	Points	Hp	Wk	Artist		Title
	221	7	7	Justin Bieber	Anyone
	215	10	8	Ed Sheeran	Afterglow
	200	1	5	Olivia Rodrigo	Drivers License
	194	3	6	The Kid Laroi	Without You
	178	11	8	Hvme		Goosebumps

After reordering I want to update the Item Ranking with the sorting order.
So Justin Bieber will be 1, Ed Sheeran will we 2 etc.

The result woud be:
Ranking	Points	Hp	Wk	Artist		Title
1	221	7	7	Justin Bieber	Anyone
2	215	10	8	Ed Sheeran	Afterglow
3	200	1	5	Olivia Rodrigo	Drivers License
4	194	3	6	The Kid Laroi	Without You
5	178	11	8	Hvme		Goosebumps

What is the right syntax in SQLite to update my database with Ranking filled with the correct order.

I have searched on internet and found the following instruction.
SELECT
Ranking,Points,Artist,Title,
RANK () OVER (ORDER BY Points desc, HP asc, Wk desc) SortOrder
FROM
Top40

Ranking	Points	Hp	Wk	Artist		Title		SortOrder
null	221	7	7	Justin Bieber	Anyone		1
null	215	10	8	Ed Sheeran	Afterglow	2
null	200	1	5	Olivia Rodrigo	Drivers License	3
null	194	3	6	The Kid Laroi	Without You	4
null	178	11	8	Hvme		Goosebumps	5

The SELECT instruction works but with a new virtual column SortOrder. I want to use the item Ranking instead.
Can I use such an instruction to update Ranking?

(2) By Larry Brasfield (larrybr) on 2021-02-21 14:42:41 in reply to 1 [link] [source]

After inserting the new list I reorder the database with SELECT ... ORDER BY Points DESC, Hp ASC, Wk DESC

More accurately, you get an ordered query result; the database is unchanged.

Without condoning the practice of back-filling transient data into a table, I can say that this example (of such practice) may be of interest:

-- Setup example table and content. CREATE TABLE DogJumps (name TEXT, jumpsOverLastWeek INTEGER, recentRank INTEGER); INSERT INTO DogJumps (name, jumpsOverLastWeek) VALUES ('Fido', 42), ('Fleaser', 1), ('Bowser', 21); -- -- Define a view on DogJumps with ranking as of view time. -- The stored recentRank is ignored; it may not be current. CREATE VIEW TopDogsNow AS SELECT name, row_number() OVER rankNow AS ranking FROM DogJumps WINDOW rankNow AS (ORDER BY jumpsOverLastWeek DESC) ORDER BY name; -- -- Place current ranking in DogJumps as recentRank. UPDATE DogJumps SET recentRank = tdn.ranking FROM (SELECT ranking, name FROM TopDogsNow) AS tdn WHERE tdn.name = DogJumps.name;

You can lookup WINDOW functions in the SQLite docs to see how that works.

(3) By Ryan Smith (cuz) on 2021-02-21 15:35:03 in reply to 1 [source]

This is an enormous sin in Database terms.

Why back-fill the data with information that is essentially a simple calculation on existing data?

The ranking is true and can be deduced of any current set of information, there is no need to "store" it in the data, yet there is a significant inset cost to achieve this, especially in development time as you have now experienced.

This will more than be adequate for the purposes, unless there is some specific thing you neglected to tell us:

CREATE VIEW Top40Ranked AS
SELECT RANK () OVER (ORDER BY Points desc, HP asc, Wk desc) AS Ranking,
       Points,Hp,Wk,Artist,Title
  FROM Top40
;

Then, if you select all from that view (or any subsection of the fields), like this:

SELECT * FROM Top40Ranked
 ORDER BY Points desc, HP asc, Wk desc;

You should get this:

Ranking	Points	Hp	Wk	Artist		Title		
1	221	7	7	Justin Bieber	Anyone		
2	215	10	8	Ed Sheeran	Afterglow	
3	200	1	5	Olivia Rodrigo	Drivers License	
4	194	3	6	The Kid Laroi	Without You	
5	178	11	8	Hvme		Goosebumps	

And best of all, when you next add new data, this view will automagically again show the correct values. PS - Even if you don't include the ORDER BY clause in the last query, the Ranks will still be correct even if the rows might be in a different order.

Does that not solve your quest exactly?

(4) By Larry Brasfield (larrybr) on 2021-02-21 15:53:53 in reply to 3 [link] [source]

This is an enormous sin ... Why back-fill the data with information that is essentially a simple calculation on existing data?

Ryan, I do not disagree with eschewing such practice. However, we cannot be certain from the OP's original post whether he/she (or possibly it's a bird) actually intends to engage in that practice. For all we can tell, without surmise as to what her/his/its data means and what result is desired, the intention is to record, in the same table, both transient data affecting ranking and a week-by-week rank which changes only upon weekly update. IOW, if that is the intention, your always-current view gives the wrong answer.

That is why I elected to answer the question posed, even though I suspected (but did not know) that such answer was a solution to a mis-framed problem.

(5) By Ryan Smith (cuz) on 2021-02-21 16:16:03 in reply to 4 [link] [source]

While I can agree with the idea that someone (of human or ornithological descent) with different intentions might end up doing something different than back-filling data, I have to say that, to my mind, the OP's first two paragraphs leaves nothing to the imagination.

I also absolutely agree that one should, in addition to any eschewing or alternate suggestion, answer the original question - but your post had already covered that grounds perfectly and I couldn't think of anything to add to it.

I guess what we should do is make it clear to the OP: Sir/Ma'am/Gentle Fowl, what you've asked CAN be achieved (see Larry's solution), but, if your description of what you need it for is precise, shouldn't be done (see Ryan's post for an alternate method).

Either way, best of luck and do ask if anything is unclear or any other questions arise.

(6) By TripeHound on 2021-02-21 16:35:32 in reply to 5 [link] [source]

Should any descendants of clade Amniotes1 actually want to store the week-by-week rankings over time, they probably should be in a different table that looks something like

WeekNo  Ranking   ArtistID
   1       1      23 (-> Justin Bieber in an "Artists" table)
   1       2      32 (-> Ed Sheeran)
  ...     ...     ...
   1      40      ??
   2       1      32 (Ed Sheeran knowns Justin Bieber of the top spot, pop-pickers)
etc.

1 According to A Bird’s-Eye View of Human Language and Evolution, the last common ancestor of birds and mammals (the clade Amniotes) lived about 310 – 330 million years ago.

(7) By Larry Brasfield (larrybr) on 2021-02-21 18:22:12 in reply to 5 [link] [source]

To be clear, I think your lesson was probably applicable and needed. I was mainly responding to "This is an enormous sin ...". In my consulting work, my more significant added value often began with "I don't think that's the problem which needs to be solved here.", followed by stating a different problem whose solution would better serve the client. Often, that view prevailed. But sometimes there were good reasonsa that a more comprehensive solution was not the best. Exploration of those issues is usually worthwhile and well received if sufficiently brief.

Of course, being hopeful of a continued (and remunerative) relationship, I would not speak of sin or offense against principles known among the more learned.b I suppose being so direct is more workable in a forum like this. After all, we can hardly be fired.


a. A common reason to prefer an ugly solution is that it better fits into an already-ugly larger design, and a fix is needed soonest.

b. Clients can be touchy, particularly when invested in a perspective that is not working. I see tact as serving their real need.

(8) By Ryan Smith (cuz) on 2021-02-21 20:10:53 in reply to 6 [link] [source]

:)

Going to be on my sign at the next "Save the pigeon" rally: "Birds are Amniotes too!"

Might even be reusable at a rally against sexism.

(9) By John Gelm (gelmjw) on 2021-02-22 15:53:46 in reply to 1 [link] [source]

Why is there no column for "week the new list is published"?

(10) By Birdy (SQLBirdy) on 2021-02-22 19:35:17 in reply to 3 [link] [source]

First of all, thank you for all the answers and yes the message is loud and clear.
I know that it is not clever to back-fill the data but that was my first attempt to get my problem solved.
I have just started using SQLite for my database and I still have to learn a lot.
SQLite is the right software for me because it is just a single standalone database and it is freeware.
I am very satisfied with the option from Ryan. It is exactly what I was looking for. Thank you for pointing me to the right direction.
Answer for John: there are some more fields in the database like Country and First charted but for my question it was not relevant.