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
0
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
0
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
0
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
0
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字段。