SQL - Update table changing one column value based on another table

Question

Hi. Sorry if the title is not as descriptive as it should be but it is kind of difficult to explain in one sentence what I am trying to do ;).

I have one table that links parent objects with its respective childs. And I have another table with all the objects (parents and childs) with its respectives images. However, the image is just set for the parents objects. I would like to update this last table and set the childs image the same image that is already set for its parent. Besides, as there is more than one image for each object, I would like to set one in particular, which I can know based on an attribute column.

My tables look something like:

RelationTable

child-id

parent-id

ImageTable

object-id

attribute-id

image-url

And here goes an example in order to clarify things:

RelationsTable

child-id | parent-id

3 | 1

4 | 1

5 | 2

ImageTable

object-id | attribute-id | image-url

1 | goodimage | image1.jpg

1 | badimage | image1b.jpg

2 | goodimage | image2.jpg

2 | badimage | image2b.jpg

3 | goodimage | no

3 | badimage | no

4 | goodimage | no

4 | badimage | no

5 | goodimage | no

5 | badimage | no

So, I would like to set the images of objects 3, 4 and 5 (child ones) to its respective parent images, but to the 'correct' ones, that is the images with 'goodimage' as attribute-id.

At the end it should look like:

1 | goodimage | image1.jpg

1 | badimage | image1b.jpg

2 | goodimage | image2.jpg

2 | badimage | image2b.jpg

3 | goodimage | image1.jpg

3 | badimage | no

4 | goodimage | image1.jpg

4 | badimage | no

5 | goodimage | image2.jpg

5 | badimage | no

Actually, I don't care if 'badimage' is set as well, but the important one is 'goodimage'.

I've been trying something like:

UPDATE ImageTable

SET image = (SELECT image-url FROM ImageTable WHERE ImageTable.object-id = RelationTable.parent-id AND ImageTable.attribute-id = 'goodimage')

WHERE ImageTable.object-id = RelationTable.child-id AND ImageTable.attribute-id = 'goodimage'

but it's not working since it is not correct SQL syntax. I don't know if I should use a variable (never used one) or if this can be done with just one SQL sentence.

Any help would be much appreciated.

Answer 1

First of all, if the relation between parent and child is 1:n, why don't you just add that information to the same table? e.g. fieldname "parentid" (if it's empty/null it's only a parent). Benefit: you don't need an additional table and can easily create a hierarchy if needed.

And for the image, I guess you want to display this somewhere in your code, but it should be easier to just modify the SELECT to get the image-url of the parent is no image is given for the child. Benefit: no need to update the table when you get new entries and it would also be possible to for some child entries to have their own image (if needed).

edit: just noticed your new comment about the open-source project part, of course this makes it hard to change the database design. But maybe it's still easier to move some of the problems to the programming side (unless this is handled by the open-source software as well).

Answer 2

something like this?

NOTES:

This could be merged into one non-subquery statement but I was lazy.

I did not test, expect typos

;WITH goodlist AS
(
  SELECT child-id, image-url
  FROM relationstable
  left join imagetable on relationstable.child-id = relationstable.child-id and attribute-id = "goodimage"
)
UPATE imagetable
 set imagetable.image-url = 
   (SELECT top 1 image-url from goodlist where child-id = imagetable.object-id) 
WHERE imagetable.image-url = "no"