0

I have to update as well as insert data in MySQL in a single query. I have two tables: one is paytransactionfailure and second is walletbalance in which I have to first get data from paytransactionfailure table and and update the status of this table and insert data in walletbalance table. Is this possible in a one query.

My first table name:

paytransactionfailure

...........................................................
id   fromid   toid    amount  refund_status  user_register
...........................................................
1      5       6        20          0             1

walletbalance

...............................................
id    user_id      balance    transaction_type
...............................................
1       5          100           credit

I want this if SELECT * from paytransactionfailure WHERE refund_status='0' and user_register='1' and fromid='5' query exists then I have to update refund_status=1 in paytransactionfailure table and at the same time I want to insert data in walletbalance table for fromid user like this

want this output

  ...............................................
    id    user_id      balance    transaction_type
    ...............................................
    1       5          100           credit
    2       5           20           credit

for this I have used below query but I have get success in updating record but I don't get success in inserting record in walletbalance.
Is this possible or I am going in a wrong direction?

UPDATE paytransactionfailure
    SET `refund_status` =1
    WHERE EXISTS (SELECT * from paytransaction WHERE refund_status='0' and `user_register`='1')
Alex
  • 4,885
  • 3
  • 19
  • 39
varun joshi
  • 461
  • 1
  • 8
  • 27
  • You can try this IF EXISTS(select * from test where id=30122) update test set name='john' where id=3012 ELSE insert into test(name) values('john'); Other approach for better performance is update test set name='john' where id=3012 IF @@ROWCOUNT=0 insert into test(name) values('john'); – John Simon Jun 21 '16 at 07:42
  • 4
    MySql is not sql-server. Which one is it? – Zohar Peled Jun 21 '16 at 07:53
  • insert on duplicate key update, or do that in the service layer using transaction. Or you can do it with db trigger, but it can cost much when service target grow larger. – Tiina Jun 21 '16 at 08:03
  • how we can achieve this using insert on duplicate key update is ? – varun joshi Jun 21 '16 at 09:21
  • 1
    I think most everyone could understand this [answer](http://stackoverflow.com/a/32468519) of mine on IODKU – Drew Jun 21 '16 at 10:49
  • 1
    You can't update a table AND insert into another in one statement. You can start a transaction and commit/rollback it when it is appropriate. I've removed the [tag:sql-server] from the tag list, since your example queries are written using mysql syntax. – Pred Jun 21 '16 at 12:32
  • @Drew, I did not read the comment from varun joshi in enough detail. (I will remove my ) – Alex Jun 21 '16 at 13:41
  • You may have a look at the merge-statement. With this you can update, insert and delete in one single statement. – Sven Jun 21 '16 at 12:18

1 Answers1

0

What you are looking for is INSERT ... ON DUPLICATE KEY UPDATE (to insert new rows or update one that exists), or REPLACE (to insert new rows and overwrite data of any that already exist).

In both cases, the statements rely on UNIQUE keys in the table, which your walletbalance table is missing, and the statements can only be performed on the same table.

You might want to look into using stored procedures to carry out these operations - or redesign the process entirely, because IMO the schema in question just wasn't designed for what you are trying to do with it.

Michał Gawlas
  • 186
  • 7