MySQL UPDATE Trouble

What's wrong with this?

UPDATE products, products_description SET products.vendors_id = '2' WHERE products.products_id = products_description.products_id AND products_description.products_name LIKE 'something%'

I basically want to update a record (vendors_id) in table A (products) if a corresponding record (products_name) in table B (products_description) starts with 'something'.

I keep getting this: You have an error in your SQL syntax near ' products_description SET products.vendors_id = '2' WHERE products.products_id =' at line 1

If I delete products_description after UPDATE products, it complaints it's an unknown table.

Any guru's know a fix?

 

You can't have 2 tables after in update like that you will need to use a sub query, something like

update products set products.vendors_id=2
where
products.product_id = (select products_id from product_descriptions 
                               where products_description.product_name 
                                LIKE 'something%')

Code (markup):

Not tested at all but it should give you an idea

 

Thanks!

Tried loads of variations of that but to no avail. Will have to dig and actually understand first I guess.

Could the sub query be expecting just one result? Mine will return 40 odd rows. On its own it works fine.

 

oh in that case use in instead of =
Also in the sub query I missed an s (product_desciptions) in the where clause

 

Yeah I fixed the typo's. Where do you think should 'in' go? Never heard of that.

Documentation says:

 

update products set products.vendors_id=2
where
products.product_id in (select products_id from product_descriptions
where product_descriptions.product_name
LIKE 'something%')

 

Well, still no luck.

Same with:

 

Both queries work independantly so no typo's or whatever...

 

Try

UPDATE products
SET vendors_id = '2' 
WHERE products_id in 
	(SELECT products_id
	FROM products_description
	WHERE products_name
	LIKE 'something%')

Code (markup):

I've tested the same syntax in MS SQL on a different table and it works fine

 

I tried that one earlier, no table names, and it still doesn't work

Almost starting to think it's phpMyAdmin's fault.

What do you run it on?

Mine is phpMyAdmin 2.3.2 on MySQL 3.23.58

 

This is the first cutdown subquery I can get to work though it's not a real one:

SELECT *
FROM products
WHERE products_id = ( 1 + 63 ) LIMIT 0, 30

But this doesn't work (I know it's a silly one but just to prove my point):

SELECT *
FROM products
WHERE products_id = (
SELECT products_id
FROM products_description
WHERE products_id = '64' )

Getting annoyed now...

Apprciate your dedication in helping me out!

 

It definetly should work, I've just tried it on 4.1.12 and no problems at all, I'm not using PHPmyAdmin but the mySQL Query Analyser program.

Sub queries are common SQL features so would be surprised if any version didn't support them, not sure what else to suggest here. Can you try it from a PHP script or better stil Query Analyzer and see if you get better error messages.

 

Bugger have a look at
http://www.unixreview.com/documents/s=8989/ur0407e/
Looks like your version doesn't like sub queries

 

Bollocks, what a waste of time. Any old school methods for me to do this (BTW in the time pissing about with this I could have updated all records manually, doh!)?

 

2 Choices:
Upgrade - Adviced
Write a PHP script so that it dynamicly creates the where clause from the sub query, it should look like

where product_id in (1, 2, 3, 4)

Code (markup):

I'm off home now so won't be able to provide any more info for an hour or so, good luck

 

Option 1: Shared Host - No Go (Will move to dedicated higher spec later this year)
Option 2: Never mind, just finished it manually.

Expect some green

 

That sounds like a miserable time!

I never really feel safe using sub queries. For something small like this, I'll do the select, put the results into a list, and then loop through that to do the updates.

Not that that's really an option if you have lots of records involved.

But I'd still rather write code than run updates manually. Sorry you had such a nightmare. I definitely feel your pain.

 

Thanks. Luckily it only involved about 40 records and I found a way to semi-automate it so in the ned I only had to manually adjust half a dozen or so.

I'll defo look out for MySQL 4.whatever on the next server!