Some Mysql Query Help

fluiduk · 8 Feb 2007 at 20:11

Hi All

Does anyone have ideas why this query isnt working? It doesnt return any errors, but it doesnt effect any rows.

Code:

UPDATE in_price_comb_temp SET stock = 'in_stock_temp.stock', date_added = 'in_stock_temp.date_added' WHERE 'in_price_comb_temp.dist_pn' = 'in_stock_temp.dist_pn'

Not sure if this even should work. Just trying to find a way of not selecting all the records and updating them one by in PHP

Thanks
Aaron

Pho · 8 Feb 2007 at 21:29

Code:

UPDATE `in_price_comb_temp` P LEFT JOIN `in_stock_temp` S ON p.dist_pn=s.dist_pn
SET p.stock=s.stock, p.date_added=s.date_added WHERE p.dist_pn=s.dist_pn

Maybe? I'm rubbish at joins though

fluiduk · 8 Feb 2007 at 21:41

Running it now, taken 600 seconds so far.. eek! 40000 records though so not surprised

Thanks

Aaron

Pho · 8 Feb 2007 at 22:03

40,000 products? I should have told you you had to do it manually

fluiduk · 8 Feb 2007 at 22:04

2000 seconds now eek

Pho · 8 Feb 2007 at 22:15

Seems to be taking forever. Does it tell you how many its done?

fluiduk · 8 Feb 2007 at 22:42

4000 seconds now, and not done. Doesnt tell me how many it has done

JonC · 8 Feb 2007 at 22:56

Code:

count (*) from in_price_comb_temp a where exists (select * from 'in_stock_temp' b
WHERE a.dist_pn = b.dist_pn)

You may need to format that a bit (I usually work with DB2), but it should return the number of rows to be effected by your SQL.

If you post what's in each of the tables, and what you're trying to do people might be bale to help. Posting random SQL statements won't help you poke holes in anything but the validity of your SQL syntax.

It's all about context!

fluiduk · 15 Feb 2007 at 11:05

Hi Jon

Thanks for the message. That query doesn't work

I can't give too much information on here, as its quite sensitive. I will give all the details I can, what would help?

Thanks
Aaron