在一个查询MYSQL中进行INSERT和UPDATE [英] INSERT and UPDATE in one query MYSQL

问题描述

我有一个简单的INSERT查询，该查询在单击按钮的事件时运行，这将获取变量并将其插入到order表中.

I have a simple INSERT query that is run on event click of a button, this gets variables and inserts them into order table.

我还想更新我的bands表以将库存减少1，直到达到0，然后显示为空或售罄，而不是转为负数，例如:-6

I also would like to UPDATE my bands table to reduce the stock by 1 until it reaches 0 then show null or sold out rather than go into negative eg: -6

要知道我可以选择哪个频段:

to know what band is selected I can use :

WHERE Band_id = $Band_id");

在同一条语句中.

当前的INSERT看起来像:

the current INSERT looks like :

"INSERT INTO orders (band_id,user_id,user_name,band_name,band_venue) VALUES('$Band_id', '$user_id', '$user_name', '$name', '$venue')";

结论

需要将我的INSERT更改为UPDATE WHILE，同时检查其当前选定的波段.

Need to alter my INSERT to also UPDATE WHILE checking that its the current selected band.

已提供答案检查

已经很好地提供了此解决方案，它很有意义，但我还不够熟练，无法理解为什么它无法正常运行.

this solution has been wonderfully provided and it makes sense but im not quite skilled enough to understand why its not functioning correctly.

 $sql = "INSERT INTO orders (band_id,user_id,user_name,band_name,band_venue) VALUES('$Band_id', '$user_id', '$user_name', '$name', '$venue')";

 $sql_create ="CREATE TRIGGER tg_ai_orders
        AFTER INSERT ON orders
        FOR EACH ROW
          UPDATE bands 
          SET stock = stock - 1
          WHERE band_id = NEW.band_id";

$sql_deleat = "CREATE TRIGGER tg_ad_orders
        AFTER DELETE ON orders
        FOR EACH ROW
         UPDATE bands 
         SET stock = stock + 1
         WHERE band_id = OLD.band_id";

mysql_query ($sql, $sql_deleat, $sql_create, $linkme)
    or die ("could not add to database");

推荐答案

如果要使用触发器方法，则需要两个触发器(用于插入和删除)

If you'll go with a trigger approach then you'll need two triggers (for insert and delete)

CREATE TRIGGER tg_ai_orders
AFTER INSERT ON orders
FOR EACH ROW
  UPDATE bands 
     SET stock = stock - 1
   WHERE band_id = NEW.band_id;

CREATE TRIGGER tg_ad_orders
AFTER DELETE ON orders
FOR EACH ROW
  UPDATE bands 
     SET stock = stock + 1
   WHERE band_id = OLD.band_id;

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于在一个查询MYSQL中进行INSERT和UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！