pset8 finance portfolio on new stock/id

Question

I'm trying to make it so when the user doesn't already own the stock, it adds it to the table. If they do already own it, it should update. The problem is when I try it, it buys 2x what it is supposed to, meaning both cases are working when only one should. I'm not sure what is causing the other one to run. This is my code

db.execute("SELECT user_id, stock FROM portfolio WHERE EXISTS UPDATE portfolio SET shares = shares + :newshares WHERE user_id = user_id AND stock = stock\
        ", user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares")

        db.execute("SELECT user_id, stock FROM portfolio WHERE NOT EXISTS INSERT INTO portfolio (user_id, stock, shares) VALUES (:user_id, :stock, :shares) \
        ON DUPLICATE KEY UPDATE shares = shares + :newshares", user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares)

Answer 1

WHERE EXISTS is conventionally used with a SELECT subquery. Although not stated explicitly, it can be inferred from the sqlite doc:

The EXISTS operator

The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.

The number of columns in each row returned by the SELECT statement (if any) and the specific values returned have no effect on the results of the EXISTS operator. In particular, rows containing NULL values are not handled any differently from rows without NULL values.

There is also an interesting and significant note in this article:

Note

• SQL statements that use the EXISTS Condition in SQLite are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.

Remember this Hint from the spec:

If an INSERT or UPDATE would violate some constraint (e.g., a UNIQUE index), then execute returns None.

Consider trying the INSERT first, and testing the result to determine if the UPDATE should be performed. Assuming there is a UNIQUE index on user_id|stock.