MySQL UPDATE Trouble

[T0PS3O]

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?

[dct]

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%')

Not tested at all but it should give you an idea

[T0PS3O]

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.

[dct]

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

[T0PS3O]

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

Documentation says:

Here is an example of a subquery:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

[dct]

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%')

[T0PS3O]

Well, still no luck.

Error

SQL-query :

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

MySQL said:

You have an error in your SQL syntax near 'SELECT products_id FROM products_description WHERE products_description.products' at line 1



Same with:



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

[T0PS3O]

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

[dct]

Try

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

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

[T0PS3O]

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

[T0PS3O]

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!

[dct]

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.

[dct]

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

[T0PS3O]

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!)?

[dct]

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)

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

[T0PS3O]

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 :)

[jimrthy]

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.

[T0PS3O]

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!