0

I'm trying to make it check if the user already owns this stock, and if they do, then to update the portfolio. I'm confused about how to get this code to work as I don't understand how to check if it exists. This is what I have, any help would be great!

            db.execute("SELECT CASE\
        SELECT user_id && stock == True\
        THEN UPDATE PORTFOLIO SET shares += newshares\
        VALUES (:user_id, :stock, :shares, :newshares)", user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares)
Improve this question

1 Answer 1

Reset to default
0

SQL code is a bit different from programming language syntax. Here's a template to give you an idea how to do what you want. You'll have to change the appropriate vars and values for your needs. ;-)

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [column x = value AND column y = value ....];

I think you would benefit a lot if you google the four main SQL commands, SELECT, INSERT, UPDATE, DELETE. They will handle the vast amount of data manipluation, along with the WHERE clause. ;-)

If this answers your question, please click on the check mark to accept. Let's keep up omn forum maintenance. ;-)

Improve this answer
4
  • This was really helpful, I got my code to db.execute("UPDATE portfolio\ SET shares += newshares\ WHERE user_id = user_id and stock = stock", user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares), but I'm still a bit confused about the conditional part. How do I make sure this gets added if it's the first time buying a stock? Do I just do the normal buy first then update?
    – Joel Banks
    Commented May 8, 2019 at 1:07
  • Well, that's where design and thought come into play. You have to make sure the user doesn't already have stock, (SELECT WHERE), or you have to dig deeper into SQL Language, as in, SELECT xxx WHERE yyy ON DUPLICATE UPDATE zzz. I believe this also requires a unique key based on the user and symbol columns. For all of this, google will be your friend. ;-)
    – Cliff B
    Commented May 8, 2019 at 1:54
  • So would it work if I did like WHERE user_id AND stock IN Portfolio But how would I make sure it only counts if they're on the same row?
    – Joel Banks
    Commented May 9, 2019 at 0:42
  • That's exactly what it does. A where clause is applied at the row level, not the table level. Using your example, a row would have to contain BOTH the given user_id and the given stock to be selected or updated. If it only contains one or the other, the row is ignored. However, if the WHERE clause had used OR instead of AND, it would have accepted a row that contained either or both values.
    – Cliff B
    Commented May 9, 2019 at 1:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .