How can I write an SQL query that will checks if a column is zero it will update it and if its not, it will move to the next row?

Question

DISCLAIMER: I'm still new to this website so I'm still learning the etiquette of the site, I apologize for any errors. Also, I previously posted a questions similar to this but some fantastic people recommended I rework my database to the current format. This was a great help however it was one step forward and one step back. I have an improved database but my question now continues to stand with a few minor tweaks.

To elaborate, I'm currently building an app that has the user create an account and login. Their information that they provided is saved into my database. My database contains two tables, one holds the users information, and one holds the users inventory, both are generated upon the completion of a create account GUI. For this question, only the second table is necessary. This table has three columns, the first is the users username, the second is their inventory slot number, and the third is the item id for the item that is in that slot. When the user creates an account, forty rows are created in this table, in each row their username remains constant. However, the slot number increments from one to forty and the item id column defaults to zero. Here is a visual representation:

Now to get to my code, when the user clicks a button, a random method gets called which sets an int variable which is current named "i" to a specific number. This number is the ID of an item in my app. At this point the user is prompted with two buttons that ask whether they want to keep the item or discard it. If they decide to keep the item I need it to be added to their inventory in the database. This is where my question comes into play. My app knows which user is logged in because when someone properly logs in the app sets their username (which is a primary key) to a global string variable which the rest of the app can user. so it knows which user to update but I need it to check through each of the rows in order, and if it finds a row with a zero in the ItemID column, it will update it to what the variable "i" currently is and end the query.

This is my current code, I'm very new to SQL but I'm trying to teach myself, I apologize if this offends you (because it's so bad):

EDIT: I've updated my code to this new query however I get an error that states java.sql.SQLException: You can't specify target table 'userinv' for update in FROM clause

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();
        }

at this point I know it needs to update the userinv table and I know it needs to do this where the users username is but I'm not sure how to write the code in between. Does anyone have any ideas?

Answer 1

This works in Oracle and should work for MySql:

update userinv set itemid = 815
where username = 'test'
and slot = (
    select min(slot) from userinv  
        where itemid = 0
        and username = 'test'
)

For more complex cases where you need the first row according to some ordering, but can't express this as a minimum this approach works on Oracle:

update userinv set itemid = 815
where username = 'test'
and slot = (
    select slot from (
        select count(*) over (partition by username order by slot) cnt, 
           slot
        from userinv  
        where itemid = 0
        and username = 'test'
    ) where cnt = 1
)

It uses analytic functions so it won't work on MySql, but there is an article how to fake them in MySQL.

With analytic functions, this should also work (didn't try, so it does contain typos and stuff)

update (
        select count(*) over (partition by username order by slot) cnt, 
           u.*
        from userinv u  
        where itemid = 0
        and username = 'test'
        order by slot
    )
 set itemid = 815
 where cnt = 1   

This accesses the table only once, which should be way faster when your table is huge.