1.15.2 SQL update minus X amount but not below 0

Discussion in 'Spigot Plugin Development' started by lostwanderer1, Sep 16, 2020 at 9:10 PM.

  1. Hey,

    I did a bit of googling but honestly I don't understand what the God's on stackoverflow are talking about.

    Is anyone good with SQL and able to help me build this string?

    I have an int column and I just want to be able to take away X amount, but if it goes below 0 then to simply set it to 0.

    An obvious workaround is to query the value, do the changes in Java then update it. But that is twice as much for the system! I was hoping I could do this inside an SQL statement.

    Cheers =D
     
  2. Keep a HashMap containing everything in the database and set the key as the player assuming this is for the player. Create a class to go with the database. Now are you need to do is

    Code (Java):
    String query = "UPDATE table_name SET xValue = ? WHERE uuid = ?";
     
  3. Unfortunately, more specific SQL queries like this are where the various implementations tend to differ.
    I took a stab at this for modern SQLite because I've played around with it a decent bit for various reasons.

    Code (SQL):
    INSERT INTO mydata(id, money) VALUES (?,
        /* MAX function selects greatest of values. */
        MAX(
            /* COALESCE function guarantees that your number is 0 instead of null if not present.
           This safeguards against initial negative value.
            You can also use IFNULL but I haven't worked with it so I'm not. */

            COALESCE((SELECT money FROM mydata WHERE id=?),0)+?,
        0)
    )
    /* SQLite's UPSERT functionality
    existing data is databasename.columnname, new data is excluded.columnname */

    ON CONFLICT(id) DO UPDATE SET money=excluded.money;
    For this particular statement, you need to set values 1 AND 2 to your ID. I'm not familiar with any syntax that allows you to tell it that certain arguments are identical. 3 will of course be your actual value difference.

    Note that if you can guarantee the value for money will already have been set (i.e. a guaranteed update instead of a "insert if not exists else update") it would be much more efficient to skip all the COALESCE bullhonkey and just use INSERT INTO mydata(id, money) VALUES (?,?) ON CONFLICT(id) DO UPDATE SET money=MAX(0, mydata.money+excluded.money);
    There are, of course, a billion and a half ways to do this. Find what works for you.

    A whole lot of fun googling will allow you to adapt this to any other SQL language. The real trick is knowing what keywords to use for what you're after.

    Edit: accidentally a couple parameters into the wrong place, fixed that.
    More edit: also fixed keywords in names of stuff for clarity
     
    #3 Jikoo, Sep 17, 2020 at 2:15 AM
    Last edited: Sep 17, 2020 at 2:31 AM