oracle - mysql如何更新一个多表查询出来的字段

Question

$sql = 'SELECT c.is_check' .
                ' FROM ' . $GLOBALS['ecs']->table('goods') . ' AS g ' .
                ' LEFT JOIN ' . $GLOBALS['ecs']->table('cart') . ' AS c ON c.goods_id = g.goods_id ' .
                ' WHERE g.enter_mode = 3105 AND c.user_id = $user_id';
                

如上 sql 语句是通过 cart 表和 goods 表的一些条件查询出 cart 表中符合条件的 is_check 字段。
我现在想将这个字段里的值统一更新为我前台传过来的 $is_chick,该怎么实现尼？

或者说能不能直接用 updata 语句实现尼？

Answer 1

Similar to this, you need to debug the sql statement yourself

update cart d set d.is_check = $is_chick

where exists(
SELECT 1 FROM ' . $GLOBALS['ecs']->table('goods') . ' AS g ' .
                ' LEFT JOIN ' . $GLOBALS['ecs']->table('cart') . ' AS c ON c.goods_id = g.goods_id ' .
                ' WHERE g.enter_mode = 3105 AND c.user_id = $user_id and c.id=d.id)

Answer 2

Update is a write lock, and it is not recommended to use complex sql.

Use your select statement to find out the primary key and put it into a list, and then use another update cart set ... id in (1,2,3,4) statement to update. If the number is greater than 200, please use batch update.

Answer 3

update a inner join (select yy from b) c on a.id =b.id set a.xx = c.yy

Put in the generated sql

Answer 4

This can be achieved through the update statement

update cart set is_check = value where id in (
    select DISTINCT id from (你查询的条件SQL语句)
)

Answer 5

I agree with @seanlook’s answer. Don’t write complex SQL in a production environment. Although @prolifes’ answer can achieve the goal, it may cause long-term locks if there is high concurrency and a large amount of data.

Our company’s general approach is to first find out the primary key based on the conditions, and then update the corresponding field value based on the primary key.

Answer 6

This requirement can be achieved through UPDATE + JOIN. The approximate SQL is as follows:

UPDATE goods AS g
LEFT JOIN cart AS c ON c.goods_id = g.goods_id 
SET c.is_check=$is_check
WHERE g.enter_mode = 3105 AND c.user_id = $user_id;

For details, please see an article I wrote recently https://mp.weixin.qq.com/s?__... Part of it is related to this issue