SQLITE - 根据另一个数据库中的数据更新多行 [英] SQLITE - Update multiple rows based on data in another database
问题描述
场景是:
主数据库:表'类型'
Id INTEGER PRIMARY KEY,
类型文字
配置数据库:表'类型'
Id INTEGER PRIMARY KEY,
类型文本
想法是配置数据库包含所有可能的类型,其中主数据库仅包含其他记录中引用的类型。我想在主数据库打开时运行检查(我在两个数据库中都存储了单独的日期时间,以指示是否需要更新,因此它仅在必要时执行)。配置数据库附加到主连接。
更新声明类似于:
UPDATE Main.Genres SET Main.Genres.Genre =(来自config.Genres WHERE Main.Genres.Id = config.Genres.Id的SELECT类型)
那不是工作,但希望它告诉你我想要完成的事情。我如何构建更新语句? SQLITE错误描述没什么用,只是告诉我'。'附近有语法错误。
干杯,
Mick
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
推荐答案
SQLLite的UPDATE
语句非常有限 - 它不支持例如,加入其他表格。
SQL作为理解的SQL更新 [ ^ ]
因此,更新列所属的表没有歧义,所以它不支持在SET
中为列名添加前缀带有表名的列表。
正确的查询是:
SQLLite'sUPDATE
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 theSET
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)
(注意:只需使用 Genre
作为 SET <中的列名称/ code> list,而不是
Main.Genres.Genre
。)
这篇关于SQLITE - 根据另一个数据库中的数据更新多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!