[SOLVED] Even tricker one

[jwwceo]

I have another tricky query which I can't seem to get working:

 

There are 4 tables, with various links to one another. I want to update the value image_path in one table, based on the values from 2 other tables. The two values for this particluar query are

 

productid: 16150

option_name: Black

 

Here is the code I have so far:

 

Thanks!!!

 

UPDATE xcart_images_W SET image_path = 'test.png'

INNER JOIN xcart_variants.variantid ON xcart_variant_items.variantid
INNER JOIN xcart_variant_items.optionid ON xcart_class_options.optionid
INNER JOIN xcart_variants.variantid ON xcart_images_W.id

WHERE

xcart_variants.productid = '16150' AND
xcart_class_options.option_name = 'Black'

[fenway]

This is just like your other post... there's no "first" table.

[jwwceo]

I don't know what you mean by "first table"???

 

The table I am trying to update is xcart_images_W. The field in that table is image_path.

[fenway]

You've interspersed your SET in the middle of a JOIN!

[jwwceo]

I guess I don't know enough about JOINS to understand what that means? Any idea how to correctly write this syntax???

[fenway]

Move the SET after the last join before WHERE.

[jwwceo]

I made the changes you said and I get this error. I really appreciate the help with this..

 

#1066 - Not unique table/alias: 'variantid'

 

Here's what I used...

 

 UPDATE xcart_images_W 
INNER JOIN xcart_variants.variantid ON xcart_variant_items.variantid 
INNER JOIN xcart_variant_items.optionid ON xcart_class_options.optionid 
INNER JOIN xcart_variants.variantid ON xcart_images_W.id 
SET image_path = 'test.png' 
WHERE xcart_variants.productid = '16150' 
AND xcart_class_options.option_name = 'Black'  

[jwwceo]

I got it working using this. I was failing to list all the tables in the UPDATE line, even if they aren't being updated

 

UPDATE xcart_images_W, xcart_variants, xcart_variant_items, xcart_class_options

SET xcart_images_W.image_path = 'test1.png' 
WHERE xcart_variants.variantid = xcart_variant_items.variantid 
AND xcart_variant_items.optionid = xcart_class_options.optionid 
AND xcart_variants.variantid = xcart_images_W.id 
AND xcart_variants.productid = '16150' 
AND (xcart_class_options.option_name = 'Black' 
OR xcart_class_options.option_name = 'Gold'
OR xcart_class_options.option_name = 'Navy')

[teng84]

ignore topic solved

[fenway]

It may be "solved", but the issue was joining the same table twice with an alias on different conditions.