我正在尝试使用以下查询更新new_table
。
不幸的是,我收到了警告The target table n2 of the UPDATE is not updatable
.我该如何解决这个问题?
UPDATE new_table n
JOIN (SELECT hash,
visits,
first_visit,
last_visit,
Datediff(last_visit, first_visit) AS date_diff,
( Datediff(last_visit, first_visit) / visits ) AS diafora
FROM new_table
WHERE Date(first_visit) >= Date_sub(Curdate(), INTERVAL 15 day)
AND Date(last_visit) >= Date_sub(Curdate(), INTERVAL 7 day)
AND visits > 1
HAVING date_diff > 0) AS n2
ON n2.hash = n.hash
SET n.diafora = n2.visits_frequency
更新
visits_frequency
是new_table中的一列。计算diafora
,visits_frequence
必须取其值。
n2 表上没有visits_frequency字段。这个领域从何而来?它是计算的还是您在选择中忘记了它?如果它来自选择,您可以尝试:
UPDATE new_table n
JOIN (SELECT hash,
visits,
visits_frequency,
first_visit,
last_visit,
Datediff(last_visit, first_visit) AS date_diff,
( Datediff(last_visit, first_visit) / visits ) AS diafora
FROM new_table
WHERE Date(first_visit) >= Date_sub(Curdate(), INTERVAL 15 day)
AND Date(last_visit) >= Date_sub(Curdate(), INTERVAL 7 day)
AND visits > 1
HAVING date_diff > 0) AS n2
ON n.hash = n2.hash
SET n.diafora = n2.visits_frequency
考虑到您希望n.visit_frequency采用 n2.diafora 计算的值,请尝试以下操作:
UPDATE new_table n
JOIN (SELECT hash,
visits,
visits_frequency,
first_visit,
last_visit,
Datediff(last_visit, first_visit) AS date_diff,
( Datediff(last_visit, first_visit) / visits ) AS diafora
FROM new_table
WHERE Date(first_visit) >= Date_sub(Curdate(), INTERVAL 15 day)
AND Date(last_visit) >= Date_sub(Curdate(), INTERVAL 7 day)
AND visits > 1
HAVING date_diff > 0) AS n2
ON n.hash = n2.hash
SET n.visits_frequency = n2.diafora