Can't get this query to work...

[JohnnyT]

Hi

Here's what I'm trying to get to work:

UPDATE product_list AS prod_list JOIN mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id SET prod_list.price = 2.23 AND mem_bg.price = 2.23 WHERE prod_list.category = 3 AND prod_list.name NOT LIKE '%Animated%'

Basically I have 2 tables called Product List and Mem Background. They have a common column in that prod_list.id is the same as mem_bg.prod_id

I'm not very good with MySql and have been reading the docs on UPDATE and JOIN all afternoon but still can't get it to work.

Do I need to use a comma instead of JOIN ?

Any help would be really appreciated.

Many thanks

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[DonQuichote]

What errors are you getting?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[JohnnyT]

No errors. Its just not doing what it should. Its changing all the prod_list.prices to 0 and not doing anything to the mem_bg.prices

???

I don't make mistakes, I'm merely beta-testing life.

 

[DonQuichote]

If you make it s SELECT query, does it return the right number of records?

Your query should not set anything to zero. Are you sure there is no other query messing things up?

Are there a triggers defined on the tables?


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[JohnnyT]

There's no other queries messing it up.

I can't do it with a SELECT because this query:

SELECT product_list AS prod_list
JOIN mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id
WHERE prod_list.category =3
AND prod_list.name NOT LIKE '%Animated%'

Gives me:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id WHERE prod_lis' at line 1


Don't know what "triggers" are but, as far as I know I don't have any....

Is it meant to be:
UPDATE product_list AS prod_list, mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id

Instead of JOIN ??

Thanks for your continued support

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[r937]

you seem to be missing the FROM keyword

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

SELECT *
       FROM product_list AS prod_list
            JOIN mem_background AS mem_bg
                 ON prod_list.id = mem_bg.prod_id
       WHERE prod_list.category =3
             AND prod_list.name NOT LIKE '%Animated%';

This should give you all the rows and fields that the UPDATE query could modify.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[JohnnyT]

Hi DonQuichote, r937,

Thanks for your help.

That query shows up 24 rows correctly...

I want to change the data in both tables though.

I'd like to change the 'price' field in both the prod_list table and the mem_bg table. I thought that my original query:

UPDATE product_list AS prod_list JOIN mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id SET prod_list.price = 2.23 AND mem_bg.price = 2.23 WHERE prod_list.category = 3 AND prod_list.name NOT LIKE '%Animated%'

Would do that ?

Any ideas why the original query isn't working?

Thanks again for your continued support.

Cheers

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[JohnnyT]

**update I've sussed it**

I've got it. I needed to get rid of the "AND" after the SET.

Here's the query thats working...

UPDATE product_list AS prod_list JOIN mem_background AS mem_bg ON prod_list.id = mem_bg.prod_id SET prod_list.price = 2.23, mem_bg.price = 2.23 WHERE prod_list.category = 3 AND prod_list.name NOT LIKE '%Animated%'

A really big thank you for all your help. I wouldn't have got there without you.

Cheers

John ;-)

I don't make mistakes, I'm merely beta-testing life.