如何在更新时连接表

发布于 2024-07-14 05:56:26 字数 903 浏览 7 评论 0原文

我有以下查询：

select      count(L.ID)
from        LA inner join L on (LA.leadid = L.ID)
where       L.status = 5
and         L.city = "cityname"
and         Date(LA.Datetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

它查找特定城市中超过 6 个月的状态为 5 的记录（其日期存储在洛杉矶）。这会返回大约 4k 个结果。我想将每条记录的状态值更新为 1，因此我的更新如下所示：

update      L, LA
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

但它会停止并锁定数据库。我怀疑存在问题，因为没有连接，但我尝试了类似的操作：

update      L, LA
from        L inner join LA on (L.OID = LA.leadid)
set         L.status = 1
where       L.status = 5 
and         L.syscity = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

显然它不起作用，因为更新中没有“来自”。

编辑> 我正在使用 MySQL

原文

I have the following query:

select      count(L.ID)
from        LA inner join L on (LA.leadid = L.ID)
where       L.status = 5
and         L.city = "cityname"
and         Date(LA.Datetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

which looks for records with status 5 in a particular city that are older than 6 months (the date for which is stored in LA). This returns about 4k results. I would like to update the value of the status to 1 on each of those records, and so my update looks like:

update      L, LA
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

but it stalls out and locks the db. I suspect there is a problem because there is no join, but I try something like:

update      L, LA
from        L inner join LA on (L.OID = LA.leadid)
set         L.status = 1
where       L.status = 5 
and         L.syscity = "cityname" 
and         Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH);

and it obviously won't work because there is no 'from' in an update.

edit> I'm using MySQL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嗳卜坏 2024-07-21 05:56:26

update      L
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         EXISTS (
  select * from LA 
  where Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)
  and LA.leadid = L.ID
)

update      L
set         L.status = 1
where       L.status = 5 
and         L.city = "cityname" 
and         EXISTS (
  select * from LA 
  where Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)
  and LA.leadid = L.ID
)

回复收藏 0 原文

百思不得你姐 2024-07-21 05:56:26

对于 MySQL，您可以使用旧的连接语法：

UPDATE  l, la
SET     l.status = 1
WHERE   l.status = 5
  AND   l.city = "cityname"
  AND   la.leadid = l.id
  AND   DATE(la.datetime) < DATE_SUB(NOW(), INTERVAL 6 MONTH)

For MySQL, you may use old join syntax:

UPDATE  l, la
SET     l.status = 1
WHERE   l.status = 5
  AND   l.city = "cityname"
  AND   la.leadid = l.id
  AND   DATE(la.datetime) < DATE_SUB(NOW(), INTERVAL 6 MONTH)

回复收藏 0 原文

缘字诀 2024-07-21 05:56:26

我会这样做：

update L
set status = 1
from LA
where L.OID = LA.leadid
and L.status = 5
and L.syscity = "cityname"
and Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)

另请参阅：

基于 ID 匹配从一个表到另一个表的 SQL 更新

I would do this:

update L
set status = 1
from LA
where L.OID = LA.leadid
and L.status = 5
and L.syscity = "cityname"
and Date(LA.SomeDatetime) < Date_Sub(Now(), INTERVAL 6 MONTH)