Sql交叉表更新 [英] Sql cross table update

问题描述

我使用的是mysql 5.7数据库。



我想创建一个工作，自动从玩家的钱包中减去租来的汽车的钱。



我在更新玩家租用的汽车时遇到了问题。只减去第一辆车的成本，联接回来的成本。交叉表是否更新了正确的方法或者我需要循环？



我尝试过：

I use a mysql 5.7 database.

I want to create a job that automatically subtracts the money for the rented cars from a player's wallet.

I got a problem with updating the players amount of money for it's rented car(s). Only the costs for the first car, the join throws back is subtracted. Is a cross table update the right way to do it or do i need a loop?

What I have tried:

UPDATE player p 
JOIN garage g ON p.id = garage.owner 
JOIN car c ON c.id = garage.car
SET p.money = p.money - c.rentrate, g.lastpaidrent = NOW()
WHERE c.rented = '1' AND TIMESTAMPDIFF(HOUR, c.lastpaidrent, NOW()) >= 24 AND p.money >= c.rentrate

推荐答案

更新p图层p

设置p.money = p.money - c.rentrate，

来自播放器p内部连接车库G on p.id = g.owner

内部联接c c在c.id = g.car

其中C.rented = 1和TIMESTAMPDIFF（HOUR，c.lastpaidrent，NOW（））> = 24 AND p。钱> = c.rentrate

UPDATE player p
set p.money = p.money - c.rentrate,
from Player p inner join garage G on p.id = g.owner
inner join Car c on c.id = g.car
where C.rented = 1 and TIMESTAMPDIFF(HOUR, c.lastpaidrent, NOW()) >= 24 AND p.money >= c.rentrate

这篇关于Sql交叉表更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！