Updating TableA with info from TableB

Databases
1

Hi,
I have this:



UPDATE nabs_databody2   SET nabs_databody2.INSTOCK =      (SELECT INSTOCK      FROM nabs_stockinfo     WHERE nabs_databody2.SKU = nabs_stockinfo.SKU)

It works, the problem is that some of the unique in nabs_databody2 are not in nabs_stock info and are returning NULL.

How can I configure it so that if nabs_stockinfo doesn’t have the SKU that nabs_databody2 has,it gets left alone?

I have been stumped for days on this.

Thanks,
Kevin

2

I posted this in PHP since I think this may be more of a php question.

3

No it’s a mysql question.

And anyway, please don’t post the same question multiple times. If you think it would be better suited for another forum, click on the red flag icon you see at the left of each post, and send your request to move it to the moderators.

4

Try the multiple-table syntax:


UPDATE nabs_databody2
LEFT OUTER JOIN nabs_stockinfo
ON nabs_databody2.SKU = nabs_stockinfo.SKU  
SET nabs_databody2.INSTOCK =  COALESCE(nabs_stockinfo.INSTOCK, nabs_databody2.INSTOCK)
5

Thank you, Guido. I will give that a try.

6

Guido,
That did it!! Thank you!! Was exactly what I was looking for. I thought it would take some lengthy php code to do.
Thank you !!!
Kevin

7

Just noticed that I posted the wrong query. I intended to post this:


UPDATE nabs_databody2
INNER JOIN nabs_stockinfo
ON nabs_databody2.SKU = nabs_stockinfo.SKU  
SET nabs_databody2.INSTOCK =  nabs_stockinfo.INSTOCK
8

Works just great. I really can’t thank you enough.