UPDATE distinct with ID

[dflow]

trying to combine and execute this

UPDATE `apartments` SET mainImage = (select DISTINCT(ImageURL) from images where ID LIMIT 1,1) WHERE InternalSupplierID=7; 

//while:Id needs to be distinct like the list bellow, 
the above inserts the first row continuously

(SELECT DISTINCT(ID)
FROM `apartments`
WHERE `InternalSupplierID` =7)

//need LEFT JOIN?

[Muddy_Funster]

Your LIMIT 1,1 is why you are only ever getting the first row every time.

 

What exactly do you want to happen?

[dflow]

i ahve a lis

Your LIMIT 1,1 is why you are only ever getting the first row every time.

 

What exactly do you want to happen?

i need to UPDATE mainimage in the apartments table with the image list foreach apartment ID, hence the first row in the list foreach apartment ID

[Muddy_Funster]

is there a field in the images table that refferences the appartment table to show which apartment the image is of?

[dflow]

is there a field in the images table that refferences the appartment table to show which apartment the image is of?

apartments.ID=images.ID

[fenway]

What are you trying to achieve?

[requinix]

UPDATE apartments
SET mainImage = (SELECT ImageURL FROM images WHERE images.ID = apartments.ID LIMIT 1)
WHERE InternalSupplierID = 7

You could write the UPDATE to use a JOIN but I think MySQL will optimize the subquery into that form anyways.

[fenway]

Why are you running this query? Is this a one-off DB cleanup? Otherwise, don't store the same value in 2 places.

[dflow]

Why are you running this query? Is this a one-off DB cleanup? Otherwise, don't store the same value in 2 places.

what do you mean?

how would i call an apartment images list?

[requinix]

Why are you running this query? Is this a one-off DB cleanup? Otherwise, don't store the same value in 2 places.

what do you mean?

how would i call an apartment images list?

I'll answer that with another question: How do you know what to set the mainImage to?