1

I have been digging for three days now trying to work this out, but I can't find any information, that I can make sense of at least, pertaining to this specific instance.

I have a PHP page updating my database, and there are no errors reported when running. This following query sets the sp.revelInvID column from the tmp_inv1 table, but it doesn't set the sp.revelSku column from the product table.

UPDATE shopifyProd sp
JOIN product prod ON prod.sku = sp.varSku 
JOIN tmp_inv1 inv ON inv.sku = sp.varSku
SET sp.revelSku = prod.sku, sp.revelInvID = inv.invID;

I have tried it also as:

UPDATE shopifyProd sp, product p
JOIN tmp_inv1 inv ON inv.sku = sp.varSku
SET sp.revelSku = prod.sku, sp.revelInvID = inv.invID
WHERE p.sku = sp.varSku

and many other horribly incorrect and failure filled ways. Inner joins, outer joins, left joins, crossed eyes and many tears. I've been testing them in phpMyAdmin as well as in my php page, but to no avail.

The main issue at hand is that I have another query I need to build which will be similar but have three or four joins. Am I just going to have to run multiple queries in order to make this work? One for each Join/Set?

Zach Decamp
  • 25
  • 7
  • Does anything in this help? https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query – IncredibleHat Jan 26 '18 at 16:51
  • 1
    I saw that post, but the problem is that they are only setting one field. I have to set one field from JOIN 1 and set another field from JOIN 2. – Zach Decamp Jan 26 '18 at 16:55
  • are you sure that the related select return a valid set for update??? – ScaisEdge Jan 26 '18 at 16:55
  • sku and varSku are they primary key or at least unique? – Du D. Jan 26 '18 at 16:55
  • @scaisEdge I can run both as separate queries and they work flawlessly, but when I try to use both together, the first fails. – Zach Decamp Jan 26 '18 at 16:58
  • @Du D varSku in the shopifyProd table are unique, sku in products is not unique but if I run the query with only joining the product table and setting the sp.revelSku column it works fine. – Zach Decamp Jan 26 '18 at 17:01
  • Er, nevermind my comment. Joins should have covered it. – IncredibleHat Jan 26 '18 at 17:02
  • @IncredibleHat I also don't understand that post because the joined tables aren't even used in the SET. Why bother joining at all there? – Zach Decamp Jan 26 '18 at 17:03
  • Well, its like a SELECT, you need all the tables in the FROM so it knows what to pull from. However you had the JOINs there already, so all tables should exist. I know this is possible... just trying to wrap my head around your tables and joins. – IncredibleHat Jan 26 '18 at 17:05
  • This suggests that despite having the tables in the JOINs, you still list them after UPDATE: http://www.mysqltutorial.org/mysql-update-join/ ... soo... `UPDATE shopifyProd sp,product prod,tmp_inv1 inv JOIN product prod ON prod.sku = sp.varSku JOIN tmp_inv1 inv ON inv.sku = sp.varSku SET sp.revelSku = prod.sku, sp.revelInvID = inv.invID;` ??? – IncredibleHat Jan 26 '18 at 17:08
  • You must test the query together .. `select * from shopifyProd spJOIN product prod ON prod.sku = sp.varSku JOIN tmp_inv1 inv ON inv.sku = sp.varSku `.. this is your relation not your separated queries .. – ScaisEdge Jan 26 '18 at 17:13
  • I tried the second query because I saw it suggested as an alternative to update without using a join "for a single join", and I added the join for the second table I need added, but it gives the error #1054 - Unknown column 'prod.sku' in 'on clause' It was just an attempt to avoid using two joins in case that was the issue. – Zach Decamp Jan 26 '18 at 17:17
  • @scaisEdge I just ran that select statement and it listed all columns from the three tables properly. All cross info matches appropriately. – Zach Decamp Jan 26 '18 at 17:22
  • then update your question and add the query (select you used) , and a data sample . – ScaisEdge Jan 26 '18 at 17:24
  • 1
    Your query works for me: http://www.sqlfiddle.com/#!9/3935b/1 Note that it will only update rows in `shopifyProd` that have a match in *both* `product` and `tmp_inv1`. If you need to handle cases where there's only a match in one or the other, you have to use `LEFT JOIN` instead of `INNER JOIN`. – Barmar Jan 26 '18 at 17:24
  • @Barmar That is probably the problem then. Every varSku match up to a product.sku, but tmp_inv1 won't have every sku. This is because not all of our products are tracked in inventory, so it will only be in the inventory table if it is tracked. I just changed to inv to a LEFT join and it worked!! Thank you so much! Can you put that in as an answer so I can accept it? – Zach Decamp Jan 26 '18 at 17:37

2 Answers2

0

When you join like this, mysql only take the first sku in product and tmp_inv1 tables and ignore the rest. Chances are the incorrect value you got from the shopifyProd is from the very first row of each sku.

You can see an example of this in the sql fiddle below.

http://sqlfiddle.com/#!9/c361b5/1

I borrowed the fiddle from Barmar, it much cleaner http://www.sqlfiddle.com/#!9/2d3212/1/0

The point is you can't join non unique column otherwise the result is unpredictable.

Du D.
  • 5,062
  • 2
  • 29
  • 34
  • I wasn't getting incorrect values, but it was only updating if the sku was in both product AND tmp_inv1. – Zach Decamp Jan 26 '18 at 17:39
  • oh that just your join, if you want either table you need left outer join on both not join (which is an implied inner join) – Du D. Jan 26 '18 at 17:49
0

You need to use LEFT JOIN if the table might not contain a match for the SKU.

UPDATE shopifyProd sp
JOIN product prod ON prod.sku = sp.varSku 
LEFT JOIN tmp_inv1 inv ON inv.sku = sp.varSku
SET sp.revelSku = prod.sku, sp.revelInvID = inv.invID;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • If there isn't a match in the inventory table tmp_inv1, then the sp.revelInvID should be NULL, so in that case would the IFNULL() be necessary? – Zach Decamp Jan 26 '18 at 17:57
  • @ZachDecamp No. I suspected it was redundant, but put it in just in case. – Barmar Jan 26 '18 at 17:58