Click here to Skip to main content
14,735,324 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The scenario is:

Main Database: Table 'Genres'
Id INTEGER PRIMARY KEY,
Genre TEXT

Config Database: Table 'Genres'
Id INTEGER PRIMARY KEY,
Genre TEXT

The idea is that the config database contains all possible genres, where the main database only contains genres that are referenced in the other records. I want to run a check whenever the main database is opened (I have separate date-times stored in both databases to indicate if the update is needed, so it only executes when necessary). The config database is attached to the main connection .

The update statement is something like:

UPDATE Main.Genres SET Main.Genres.Genre = (SELECT Genre from config.Genres WHERE Main.Genres.Id = config.Genres.Id)

That doesn't work, but hopefully it shows you what I am trying to accomplish. How do I need to structure that update statement? The SQLITE error description doesn't help much, just telling me I have a syntax error near '.'.

Cheers,

Mick
Posted
Updated 17-Feb-20 2:10am
Comments
Richard Deeming 5-Nov-15 9:35am
   
Try using just the column name in the SET part:
UPDATE Main.Genres SET Genre = (SELECT Genre from config.Genres WHERE Main.Genres.Id = config.Genres.Id)
Midi_Mick 5-Nov-15 9:46am
   
Yep - that did the trick. Ta muchly.
Richard Deeming 5-Nov-15 9:48am
   
OK, I'll post it as an answer to move your question off the "unanswered" list. :)

1 solution

SQLLite's UPDATE statement is quite limited - it doesn't support joining to other tables, for example.
SQL As Understood By SQLite | UPDATE[^]

As a result, there's no ambiguity as to which table the updated column belongs to, so it doesn't support prefixing the column names in the SET list with the table name.

The correct query is:
UPDATE 
    Main.Genres 
SET 
    Genre = (SELECT Genre from config.Genres WHERE Main.Genres.Id = config.Genres.Id)

(NB: Just using Genre as the column name in the SET list, rather than Main.Genres.Genre.)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900