在一个查询 MYSQL 中插入和更新 [英] 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 然后显示 null 或售罄而不是变成负数，例如:-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 中插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！