Sub-Query not working right

Databases
1

Hi,

I’m trying to run a set of sub-queries,

UPDATE glinks_Wants
	SET
	 want_price_eur = (SELECT eur FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency),
	 want_price_gbp = (SELECT gbp FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency),
	 want_price_chf = (SELECT chf FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency),
	 want_price_usd = (SELECT usd FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency),
	 want_price_aud = (SELECT aud FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency)

…but for some reason nothing is getting set in the glinks_Wants table. I’ve tried it with a simpler version:

UPDATE glinks_Wants SET want_price_gbp = (SELECT gbp FROM glinks_Costs WHERE amount = glinks_Wants.want_price AND glinks_Costs.original_currency = glinks_Wants.want_currency)

…and the same problem. However, if I run the sub-query with values in it, then it gets a

SELECT gbp FROM glinks_Costs WHERE amount = "79.99" AND glinks_Costs.original_currency = "eur"

Any suggestions as to what may be wrong? I really don’t wanna have to go down the route of doing a whole script to update them one by one (a single query is much more appealing!)

TIA

Andy

2

you didn’t mention which database system this is for, but don’t worry, there were a couple of clues in your sql to lead to the conclusion that it’s mysql

therefore, do a mysql joined update

UPDATE glinks_Wants
INNER
  JOIN glinks_Costs
    ON glinks_Costs.amount = glinks_Wants.want_price
   AND glinks_Costs.original_currency = glinks_Wants.want_currency
SET    glinks_Wants.want_price_eur = glinks_Costs.eur
     , glinks_Wants.want_price_gbp = glinks_Costs.gbp
     , glinks_Wants.want_price_chf = glinks_Costs.chf
     , glinks_Wants.want_price_usd = glinks_Costs.usd
     , glinks_Wants.want_price_aud = glinks_Costs.aud
3

Thanks - sorry, forgot to mention that =)

Its weird - I still don’t get an update on one of my rows. Maybe there is something deeper going on. I’ll do some more digging later :slight_smile:

Thanks again

4

Ah - got it!

For some reason, the values in the glinks_Costs table were DECIMAL (10,2), but the ones in the glinks_Wants table were FLOAT… so I guess that was stopping it from matching (the only one it was leaving blank, was the one with a decimal in, so thats what got me on to that track:))

Thanks again!