MySQL - update with inner join is creating nulls

user_571723 0
Viewed 300

I am trying to do an update of values from one stock table into another stock table. However, some of the values are getting copied over as NULL because that stock does not exist in the source table. I thought that INNER JOIN only looked at the values that were shared between both tables. My allStocks table has a lot more stocks than divStocks, but there are a few stocks in divStocks that don't exist in allStocks. I just want to copy the prices over from allStocks into divStocks and not overwrite any prices with NULL.

This is my current query:

UPDATE `divStocks` ds INNER JOIN `allStocks` als ON
`ds`.`tickerSymbol` = `als`.`tickerSymbol` SET `ds`.`price` =
`als`.`price`, `ds`.`priceAsOf` = `als`.`priceAsOf`;
inner-join mysql
4 Answers

I would say the simplest solution would be to add a WHERE to your query to specifically rule out rows that don't have a price in the allStocks table. Doing it this way adds to the readability of the query as compared to using something like a RIGHT JOIN which is fairly uncommon.

UPDATE `divStocks` ds 
INNER JOIN `allStocks` als ON `ds`.`tickerSymbol` = `als`.`tickerSymbol` 
SET `ds`.`price` = `als`.`price`, `ds`.`priceAsOf` = `als`.`priceAsOf`
WHERE `als`.`price` IS NOT NULL;
user_112933870

You could use COALESCE to handle cases that values in allStocks are nullable.

UPDATE `divStocks` ds 
INNER JOIN `allStocks` als ON `ds`.`tickerSymbol` = `als`.`tickerSymbol` 
SET `ds`.`price` = COALESCE(`als`.`price`,`ds`.`price`)
   ,`ds`.`priceAsOf` = COALESCE(`als`.`priceAsOf`,`ds`.`priceAsOf`);
user_50708790

If you want to update records from divStocks table which only exist in allStocks table you should use RIGHT JOIN instead of INNER JOIN. For example:

UPDATE `divStocks` ds 
RIGHT JOIN `allStocks` als ON `ds`.`tickerSymbol` = `als`.`tickerSymbol` 
SET `ds`.`price` = `als`.`price`, `ds`.`priceAsOf` = `als`.`priceAsOf`;
user_102379500

IFNULL? That is IF you want to keep as an Inner Join

UPDATE `divStocks` ds 
INNER JOIN `allStocks` als ON`ds`.`tickerSymbol` = `als`.`tickerSymbol`
SET `ds`.`price` = IFNULL(`als`.`price`, `ds`.`priceAsOf`), 
`ds`.`priceAsOf` = IFNULL(`als`.`priceAsOf`, `ds`.`priceAsOf`);
user_97768980
Related