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

Question

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.

Answer 1

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.