MYSQL多表更新

Question

有三张表：

tbl_order

id    订单ID
uid   用户ID
need_credit    订单所需积分
status    订单状态
....

tbl_coupon

id
uid
used_order    优惠码所使用到的order.id
used    0:未使用/1:已使用/2:已标记为不可用
....

tbl_member

id    用户ID
credit    用户积分
....

条件：传入$order_id

把tbl_order中
    id = {$order_id}
        的status标记为2
把tbl_coupon中
    used = 2 AND used_order = {$order_id}
        的 used标记为0, used_order 标记为 ""
把tbl_member中
    id = tbl_order.uid    /* 这里的tbl_order.uid是上面tbl_order.id={$order_id}那条数据的tbl_order.uid */
        的 credit += tbl_order.need_credit

需要由一句SQL来完成

请教了老师，用储存过程来分步实现了这个需求。
存储过程就是将若干sql封装成的一个用来调用的“函数”
虽然已经可以暂时告一段落，仍然期待着更完美的方案...

Answer 1

Tested:

update tbl_order as a, tbl_coupon as b, tbl_member as c set a.status = 2, b.used = 0, b.used_order = '', c.credit = (c.credit + a.need_credit) where a.id = {$order_id} and b.used = 2 and b.used_order = {$order_id} and c.id = a.uid 

I have a question. Is the logic of the above mysql statement the user's order cancellation action? Restore the used coupons, return the user's used points, and then modify the order status.

Answer 2

update table1 as a, table2 as b, table3 as c 
set a.status = ..., b.used = ..., c.used_order = ...
where 条件

What is easy to overlook is that all the tables to be used should be introduced in the very beginning update.

Answer 3

Personally I think the answer given by the teacher is not very reliable
There is nothing wrong with using stored procedures for this thing, but as we all know, stored procedures consume MySQL resources and are difficult to maintain

What you describe is actually a complete transaction,
Use mysql transactions to implement it
I don’t know which programming language you are using, but there should be methods that support transactions.

Answer 4

1）感觉还是使用存储过程比较好的方法，多个表更新就是存储过程的一个使用情形，主要存储过程的优缺点，以下的链接中有说明。
http://database.51cto.com/art/201108/281777.htm
2）直接使用UPDATE操作的话，对于不同的{$order_id}，那么就需要每次重新写一遍UPDATE语句，这样容易出错。当然你也可以在UPDATE中引用{$order_id}这个变量，在每次使用的时候，重新用SET设置{$order_id}的值
3）至于事务，个人的理解在本操作中题目也没有明确要求一定要保证操作过程的原子性，也没有必要提及吧。如果要求操作时，可能会有其他的程序访问表，当然使用事务是必要的