SQLITE - Update multiple rows based on data in another database

Question

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 5-Nov-15 1:19am

Midi_Mick

Updated 17-Feb-20 1:10am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2015-11-05T03:51:00

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:

SQL

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.)