CODEWITHSUNDEEP
mysqlselect
Bryan
Bryan

Reputation: 17581

SQL Update Where Statement

I have a table of images with a corresponding listing_id and position.

image_name, listing_id, position

If a listing_id does not have an image in position 1, I'd like to take the lowest position for that listing_id and set it to position 1.

Something like:

update media_table set position = 1 where ...

Upvotes: 0

Views: 312

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656361

With a syntax fix by @ypercube, the query now works for MySQL:

UPDATE media_table m
JOIN (
    SELECT listing_id, min(position) AS position
    FROM   media_table
    GROUP  BY listing_id
    HAVING min(position) > 1
    ) x ON (m.listing_id, m.position) = (x.listing_id, x.position)
SET    m.position = 1;

Explanation:

In the subquery find all linsting_id where the minimum position is greater than 1. Use these findings to update the rows right away. I assume (listing_id, position) is unique.

See working demo for MySQL.

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Give this a try:

update images i1,
( select i1.image_name, i1.listing_id from images i1
  left join images i2
  on i1.listing_id = i2.listing_id and i1.position > i2.position
  where i2.position is null and i1.position != 1
) f
set position = 1
where i1.image_name = f.image_name and i1.listing_id = f.listing_id;

This assumes you can identify a record by image_name, listing_id. If you can do so by image_name then the query would be simpler as you can remove the listing_id comparison and selection.

Upvotes: 0

Teja
Teja

Reputation: 13524

UPDATE media_table
SET position=(SELECT MIN(position) FROM media_table)
WHERE listing_id IS NULL;

Upvotes: 0

Related Questions