SQlite提高查询效率 [英] SQlite improve query efficiency

问题描述

我有两个结构相同的表，每个表大约有1万行:

I have two tables that are structurally identical and roughly 10k rows each:

db1.people         db2.people
***************    ***************
name  |  number    name  |  number
---------------    ---------------
mike  |            bob   |  
john  |            Kev   |  45
mark  |            mark  |  16
***************    ***************

我的目标是在两个表中找到同一个人，并将 number 值从db2复制到db1.进行了一些骇客操作，但我认为我可以正常工作:

my goal is to find the same person in both tables and copy the number value from db2 to db1. It took some hacking but i think i have that working:

UPDATE people SET number = (SELECT number FROM db2.people WHERE number > 0 AND name = main.people.name);

那行得通，但是非常沉重.我认为它的作用如下:

And that works, but its very heavy. I think its doing the following:

• 遍历db1.people.names中的所有10k名称
• 在db2.people.names中找到相同的名称
• 如果已填充db2.people.number，则会将该值放入main.people.number

在db2的1万行中，捕获的只有200条左右.people被填充了一个数字.

the catch is only about 200 of the 10k lines in db2.people are populated with a number.

这就是我不在同盟中的原因；我如何将上述查询限制为仅麻烦运行db2.people.number中具有相应数字值的名称?

This is where i am out of my league; how do i limit the above query to only bother running for names that have corresponding number value in db2.people.number?

那应该使我的10k ^ 10k达到200 ^ 10k

That should bring my 10k^10k to 200^10k

谢谢

推荐答案

我阅读了SQLITE支持exist子句

I read SQLITE supports the exists clause

UPDATE people 
SET number = 
(SELECT number FROM db2.people WHERE number > 0 AND name = main.people.name)
where exists 
(SELECT number FROM db2.people WHERE number > 0 AND name = main.people.name);

这篇关于SQlite提高查询效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！