mysql - Transact SQL减量表值，然后返回MYSQL的项目

在1个服务器请求sql中，如何检查表的值是否大于零，如果该值大于零，则减小该值，然后返回该表的行。

counthere = SELECT COUNT(*) FROM TableName WHERE ColumnName1 > 0 AND ColumnName2 = 'xx';
if (counthere > 0)
{
    valuehere = SELECT ColumnName1 FROM TableName WHERE ColumnName2 = 'xx';
    UPDATE TableName SET ColumnName1 = (valuehere - 1) WHERE ColumnName2 = 'xx';
    SELECT * FROM TableName WHERE ColumnName2 = 'xx';
}

我想使用Transact-SQL对服务器执行1个请求。这可能吗？你能帮我一个例子吗？谢谢大家。

我使用了@Arion的解决方案，但仍然出现此错误。

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT *
           FROM inventorylist
           WHERE BarcodeNum' at line 1
[Err] IF EXISTS(SELECT *
           FROM inventorylist
           WHERE BarcodeNumber = '9555440808279' AND NotificationQuantity > 0)
THEN
      UPDATE inventorylist
      SET NotificationQuantity = NotificationQuantity - 1
      WHERE BarcodeNumber = '9555440808279'

      SELECT *
      FROM inventorylist
      WHERE BarcodeNumber = '9555440808279'
END
[Msg] Finished - Unsuccessfully
--------------------------------------------------

最佳答案

如果我了解你的权利。您想要这样的东西：

IF EXISTS(SELECT * FROM TableName WHERE ColumnName1 > 0 AND ColumnName2 = 'xx')
    THEN
        UPDATE TableName SET ColumnName1 = ColumnName1-1 WHERE ColumnName2 = 'xx'
        SELECT
            *
        FROM
            TableName
        WHERE
            ColumnName2 = 'xx'

END IF

希望这可以帮助

关于mysql - Transact SQL减量表值，然后返回MYSQL的项目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8573231/

transact

mysql - Transact SQL减量表值，然后返回MYSQL的项目