在两个不同的表-MySQL中更新两个不同列的查询

问题描述：

I am working on upwork type project and want to create an update query for bids. User has free bids (which he gets by default per month) and pro bids which he can buy for certain amount.
Now if he would apply on any job then I want to update -1 for job_quota (if it is not 0 already), then -1 from pro bids in ascending order.

My table structure (in user table):

users

job_quota -------->id

12---------------->53

Buy_bids

id---->no_bids--->amount_paid--->user_id 

1-----> 20------------>20---------------> 53

2-----> 20------------>20---------------> 53

3-----> 20------------>20---------------> 53

I have an idea like this - but I do not how to implement it into MySQL query:

$query=<<< SQL
update users set job_quota=job_quota-1 if ( job_quota !=0 )
else update buy_bids set no_bids =no_bids-1 where user_id=53
SQL;

Can anyone suggest me how can I achieve it, I am not looking for accurate solution but I need your help and suggestion what should I try to achieve this.

Many Thanks.

我正在进行upwork类型项目，并希望为出价创建更新查询。用户有免费出价（其中他默认情况下每月获得）和专业出价，他可以购买一定金额。
如果他申请任何工作，那么我想更新-1 code> for job_quota（如果是已经不是0），然后按照升序排列的专业出价 -1 code>。 p>

我的表结构（在用户表中）： p>


 用户 strong>  p> 
 
 
  job_quota --------＆gt; id 
 
12 ---------  -------＆GT; 53 
 代码>  PRE> 
 
   Buy_bids 强>  p> 
 
 
  ID  ----＆gt; no_bids ---＆gt; amount_paid ---＆gt; user_id 
 
1 -----＆gt;  20 ------------→20 ---------------＆GT;  53 
 \ N 2  -   -   -   - ＆GT;  20 ------------→20 ---------------＆GT;  53 
 \ N3 -----＆GT;  20 ------------→20 ---------------＆GT;  53 
  code>  pre> 
 
 我有这样的想法 - 但我不知道如何将它实现到MySQL查询中： p> 
 
 
  $查询=＆LT;＆LT;＆LT;  SQL 
update用户设置job_quota = job_quota-1 if（job_quota！= 0）
else update buy_bids set no_bids = no_bids-1其中user_id = 53 
SQL; 
  code>  pre> 
 
 任何人都可以建议我如何实现它，我不是在寻找准确的解决方案，但我需要你的帮助和建议我应该尝试实现这一点。 p> 
 
 
非常感谢。 p  > 
  div>

答

Something Like this?

Update users ,buy_bids 
set users.job_quota = case when users.job_quota !=0 then 
                                users.job_quota-1
                      else users.job_quota end,
    buy_bids.no_bids =case when users.job_quota =0 then 
                                 buy_bids.no_bids-1
                     else buy_bids.no_bids end
WHERE users.user_id=buy_bids.user_id        
and users.user_id=53;

This is not tested. Just a suggestion. I'm not sure about the else condition,so just added users.job_quota =0 . Change that accordingly.

答

This is my proposal:

UPDATE users
    IF (job_quota NOT IN 0 )
    THEN  SET job_quota = -1
    ELSE (UPDATE buy_bids SET no_bids = no_bids -1 
          WHERE users.id = 53)
WHERE id= 53

答

$stmt = $mysqli->query('
    update users 
    set job_quota=job_quota-1 
    where job_quota != 0 
      and user_id=53
');

if ($stmt->affected_rows == 0) {
    $mysqli->query('
        update buy_bids 
        set no_bids =no_bids-1 
        where user_id=53
    ');
}

在两个不同的表-MySQL中更新两个不同列的查询

相关推荐