mysql 查询并insert,在一个查询MYSQL中INSERT和UPDATE

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

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");

in the same statement.

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')";

Conclusion

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

ANSWER PROVIDED CHECK

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;

Here is