SQL - Replace an entry based on one field

Question

I have a table that I am looking to refresh the existing information without creating new information (unless an entry does not already exist).

This is in the context of stocks. One of the fields is a ticker. I have a python program that is running through a list of tickers and getting updated data.

If, for example, $MMM's market cap has changed from $200B to $300B, what syntax would I use to ensure that SQL finds the existing row for $MMM, and updates existing information without creating a new row?

It'll be more helpful to community to better understand if you provide more details about your problem and what you have tried so far. You can use a Stored procedure that'll check whether record already exist if yes then update else Insert new row. — abhijeet chavan, Aug 01 '20 at 05:14
Or you can use insert trigger as well that'll check if record exists is yes then update else insert — abhijeet chavan, Aug 01 '20 at 05:31
Does this answer your question? [SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error](https://stackoverflow.com/questions/12639407/sql-if-exists-update-else-insert-syntax-error) — abhijeet chavan, Aug 01 '20 at 05:31

score 0 · Answer 1 · answered Aug 01 '20 at 11:52

This sounds like insert . . . on duplicate key update. First, though, you need a unique index or constraint:

create unique index unq_stocks_ticker on stocks(ticker);

Then you can use:

insert into stocks (ticker, marketcap)
    values (?, ?)
    on duplicate key update marketcap = values(marketcap);

Caius Jard · Answer 2 · 2020-08-01T05:21:21.593

An UPDATE query is incapable of creating a new row, so perhaps like:

UPDATE stocks SET marketcap = 300000000000 WHERE symbol = '$MMM'

Your footnote "unless it doesn't exist" means you probably then need to examine how many rows this altered and if it's 0 then run:

INSERT INTO stocks(marketcap, symbol) VALUES(300000000000, '$MMM')

It doesn't really matter which way round you do these; if you have a key on symbol you won't get duplicates, you'll get a fail to insert which you could then use to trigger an update. Ideally though you'd look at the likelihood of failure of each and go with the option of putting the least often failing option first. If you will update 10000 symbols 100 times a day each but only insert maybe 100 new symbols a day then put the update first. If you will create 10000 new symbols a day and update them once a year, put the insert first. This ensures the least time is wasted on operations that have no effect/use resources because they raise an error

SQL - Replace an entry based on one field

2 Answers2