如何在执行Update语句时设置变量?
我好像搞不懂语法。我使用这个@tp
变量来保存计算结果,它将在when
条件下使用。
我想要这样的东西:
UPDATE users IU
INNER JOIN activity_stats DAS
ON (IU.user_id=DAS.for_user_id), set
@tp:= IU.total_subscription_time - (unix_timestamp()- IU.start_date),
IU.time_period_left = CASE WHEN @tp > 0 THEN @tp ELSE 0 END,
IU.used_time = (unix_timestamp() - IU.start_date),
IU.status = CASE WHEN @tp > 0 THEN 'A' ELSE 'I' END,
DAS.total_time_used = DAS.total_time_used + IU.used_time
WHERE IU.time_period_left > 0
AND IU.status='A'
但是它说语法是错误的:
最佳答案
你能试试这个吗?
UPDATE users IU
INNER JOIN activity_stats DAS
ON (IU.user_id=DAS.for_user_id),
IU.time_period_left = CASE WHEN (IU.total_subscription_time - (unix_timestamp()- IU.start_date)) > 0 THEN
(IU.total_subscription_time - (unix_timestamp()- IU.start_date)) ELSE 0 END,
IU.used_time = (unix_timestamp() - IU.start_date),
IU.status = CASE WHEN (IU.total_subscription_time - (unix_timestamp()- IU.start_date)) > 0 THEN 'A' ELSE 'I' END,
DAS.total_time_used = DAS.total_time_used + IU.used_time
WHERE IU.time_period_left > 0
AND IU.status='A'