1

I have 2 tables - a Tune table and a an Artist table. Tune contains a list of tunes and each tune has an ArtistID. What I want to do it total up the tunes for each artist and then update the Artist table with those values. I cannot get my SQL to work.

This is what I have so far:

UPDATE Artist
SET
  TuneCount=c
FROM 
(
  SELECT ArtistID AS a, COUNT() AS c 
  FROM Tune 
  GROUP BY ArtistID
)
--WHERE _id=a

Can somebody please help me? SQLite doesn't seem to like the first FROM keyword.

SparkyNZ
  • 6,266
  • 7
  • 39
  • 80
  • because `Sqlite` doesn't support joins in `update` statements yet. http://www.sqlite.org/lang_update.html – John Woo Aug 06 '14 at 02:37

1 Answers1

3

FROM doesn't belong in an UPDATE query. First see these very helpful SQLite syntax diagrams that walk you through what's possible with in a query statement:

http://www.sqlite.org/syntaxdiagrams.html#update-stmt

It looks to me like what you're trying to do is change TuneCount to the number of tunes for that artist.

Basically we're going to replace "c" in your update query with the nested query you wrote, like this:

UPDATE Artist
SET
  TuneCount=(
  SELECT COUNT(*)
  FROM Tune t
  WHERE t.ArtistID = Artist._id
)
Scott
  • 3,663
  • 8
  • 33
  • 56
  • That looks simpler and correct but its still not working. It doesn't like the 'a' alias for some reason. – SparkyNZ Aug 06 '14 at 02:41
  • This seems to work. ArtistID is actually "_id" in my Artist table but I changed it for clarity: UPDATE Artist SET TuneCount=( SELECT COUNT() FROM Tune t WHERE t.ArtistID = Artist._id ) – SparkyNZ Aug 06 '14 at 02:43
  • You're right - it doesn't look like you can alias the update table. I'll edit the answer to match what works for you. – Scott Aug 06 '14 at 02:44
  • FYI sqlite now supports UPDATE FROM – phil294 Jul 22 '21 at 17:46