CODEWITHSUNDEEP
mysqlsqlinsertsql-update
Markus
Markus

Reputation: 407

SQL get, edit and update data

I'm trying to get an int, add another int and write the new data in one sql command. How can I do this? Here is my solution approach but I does not work.

update accounts set credit = ((select credit from accounts where id = 1679407)+1) where id=1679407;

The error looks like this.

Error Code: 1093. Table 'accounts' is specified twice, both as a target for 'UPDATE' and as a separate source for data  0.125 sec

Upvotes: 2

Views: 51

Answers (2)

Harshil Doshi
Harshil Doshi

Reputation: 3592

The problem with your approach is that you are updating the same table from which you are retrieving the data at first. As per the quotation given in this documentation

You cannot update a table and select from the same table in a subquery.

Though there is a workaround for that as following

 Update accounts
      set credit = 
          ( (select a.credit from 
                         (select *from accounts)as a where id=1679407)+1) 
                                                                  where id=1679407;

Although it's quite complicated way to achieve much simpler task of yours as you just want to increment the credit by 1 and that only for a particular id. Also the way shown above may cause performance issues sometimes.

Hence the easiest and the most efficient way to do your task is the way the @Gordon Linoff gave in his answer.

update accounts
    set credit = credit + 1
    where id = 1679407;

Hope you've learned something from this answer apart from finding the solution of your problem.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You seem to want this:

update accounts
    set credit = credit + 1
    where id = 1679407;

Upvotes: 4

Related Questions