-1

I know this question is fairly similar to a few others that have been asked but I can't seem to find a solution while looking through their answers. Most of the questions that I could find dealt with delete queries and not update queries. I have been stuck on this small bug for a few days now and I'm hoping to get a quick answer without getting in trouble for asking this question. (I'm still a little new here so I apologize if I'm doing something very wrong). I'm trying to write a query that updates a checks someones inventory for an empty slot and adds an item to it. In this case an "empty slot" is when an itemID is equal to zero and a new item is any other number. Once this is completed the query will end. I've gotten some help writing the query and I believe it is close to being done but I have a small error that I can't seem to figure out.

Here's my code:

try{
            //get connection to database
            Connection con = DataBaseConnect.getConnection();
            //create a statement
            PreparedStatement addInfo = con.prepareStatement("UPDATE userinv SET "
                    + "ItemID = "+i+" "
                    + "WHERE 'Username' = '"+LoginController.userLog+"' "
                    + "AND Slot = ("
                    + "SELECT MIN(Slot) FROM userinv "
                    + "WHERE 'ItemID' = 0 "
                    + "AND 'Username' = '"+LoginController.userLog+"')");
            //process result set
            addInfo.executeUpdate();
            }
            catch(Exception e){
                e.printStackTrace();
            }
    }

ERROR

You can't specify target table 'userinv' for update in FROM clause
Shadow
  • 33,525
  • 10
  • 51
  • 64
CRSoftware33
  • 3
  • 7
  • 1
    Is this mySQL or Oracle? – BobC Mar 12 '17 at 23:42
  • I apologize if I made an error while posting my question, while writing tags it gave me a prompt that explained that sql questions have a better chance of being seen if you add the oracle tag. I'm really new to this site and sql as well. I'm currently using mySQL to answer your question. – CRSoftware33 Mar 12 '17 at 23:46
  • No worries. It's just that the answer will be different depending on that. – BobC Mar 12 '17 at 23:57
  • It does not matter whether you use delete or update, the logic of the answer is the same: either you use join instead of subquery or add another layer of subqueries. – Shadow Mar 13 '17 at 00:02
  • 1
    Possible duplicate of [You can't specify target table for update in FROM clause](http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) – Shadow Mar 13 '17 at 00:04

2 Answers2

0

First get all data in MIN(Slot) FROM userinv in a list or array and then

foreach thoose data make an update where username LoginController.userLog

EAK TEAM
  • 5,726
  • 4
  • 30
  • 52
  • Just to add some context that may be helpful, my Slot column in my database increments from 1 to 40 for every user. And would this solution update every slot that has a zero in it or would it only update the first one it finds? – CRSoftware33 Mar 12 '17 at 23:50
  • if you update all data in the created list or array it will update everything that array holds p.s if you find this solution helpfull please upvote or mark as solved to answer , thanks – EAK TEAM Mar 12 '17 at 23:56
0

You have multiple errors in this SQL:

UPDATE userinv
    SET ItemID = @i
    WHERE 'Username' = '@UserName' AND
          Slot = (SELECT MIN(Slot)
                  FROM userinv 
                  WHERE 'ItemID' = 0 AND 'Username' = @UserName
                 );

Note: I've replaced the literal values with parameters. That really is the better way to go.

What you want is:

UPDATE userinv ui JOIN
       (SELECT MIN(Slot) as min_slot
        FROM userinv
        WHERE username = @UserName AND ItemID = 0
       ) u
       ON ui.Slot = u.min_slot and ui.UserName = @UserName
    SET ui.ItemID = @i
    WHERE'Username = @UserName ;

Do not surround column names with single quotes!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786