单个mysql查询，根据另一个表的选择数量更新数量

i have the following 2 queries that i was wondering if i can merge into a single statement.

我有以下2个查询,我想知道我是否可以合并为一个语句。

$sql = "SELECT sale_qty FROM sale_device WHERE id = '$id' ";

and

$sql = "UPDATE device SET qty = $sale_qty WHERE id = $deviceId";

i want to run a select query to get the current quantity of a device from a row, then use that value minus one in a update query to set the new quantity, but don't allow it under 0

我想运行一个选择查询来从一行获取设备的当前数量,然后在更新查询中使用该值减一来设置新数量,但不允许在0以下

is this possible or advised to join? or would it be easier to just run 2 queries?

这是可能的还是建议加入?或者只运行2个查询会更容易吗?

2 个解决方案

#1

Yes. I think this does what you want:

是。我认为这样做你想要的:

UPDATE device
    SET qty = (SELECT greatest(sale_qty - 1, 0)
               FROM sale_device
               WHERE id = '$id'
              )
    WHERE id = $deviceId

#2

If I'm understanding your question correctly, I think this should work:

如果我正确理解你的问题,我认为这应该有效:

UPDATE Device D
   JOIN Sale_Device SD ON D.Id = SD.DeviceId
SET D.qty = GREATEST(SD.sale_qty-1,0)
WHERE SD.Id = '$id'

This assumes the Sale_Device table has a DeviceId field.

这假设Sale_Device表具有DeviceId字段。

#1