How to make Sure each playerid has at least 1 Primary Image if he has more than 0 images (mysql)

I'm working with the images table. I need to make sure each player has a primary image if he has more than 1 image. In the following example dataset, this would update either myimage.jp开发者_高级运维g or hisimage.jpg's primary to 1 and only1image.jpg's primary to 1.

Table:  Images

Playerid - 16
Image  - myimage.jpg
primary - 0

Playerid - 16
Image  - hisimage.jpg
primary - 0

Playerid - 17 
Image - only1image.jpg
primary - 0

Playerid - 18 
Image - jamison.jpg
primary - 1

I'm assuming there must be a Player table? If so, you could use the following...

(This is in MS SQL Server syntax, but the logic should hold in MySQL.)

UPDATE
  Images
SET
  Primary = 1
FROM
  Player
INNER JOIN
  Image
    ON Image.ID = (SELECT MIN(lookup.ID) FROM Image AS lookup WHERE lookup.playerID = Player.ID)
WHERE
  NOT EXISTS (SELECT 1 FROM Image AS lookup WHERE lookup.playerID = Player.ID AND lookup.Primary = 1)

This deliberately chooses the image with the lowest ID to set as primary. Alter the correlated sub-query to implement different logic.

EDIT

A version that doesn't use the Player table, but possibly slower (It would need testing)...

UPDATE
  Image
SET
  Primary = 1
WHERE
  ImageID = (SELECT MIN(lookup.ID) FROM Image AS Lookup WHERE lookup.PlayerID = Image.PlayerID)
  AND NOT EXISTS (SELECT 1 FROM Image AS Lookup WHERE lookup.PlayerID = Image.PlayerID AND lookup.Primary = 1)

Here's a spin off of @Dems, this just makes more sense to me

UPDATE Images SET `primary` = 1 WHERE Player IN (
    SELECT Player FROM (
        SELECT Player, MAX(`primary`) as max_prim FROM Images GROUP BY Player
    ) as MPrim WHERE max_prim = 0
)

Alternatively you could set primary to be 1 by default, and create a uniqueness by (Player,primary) and add an ON DUPLICATE KEY UPDATE primary=0

Consider a refactoring of your tables.

Have a column in your player table that points to the image row which is the primary image, like this:

player
---------
player_id
primary_image_id


image
---------
image_id
player_id

That way, a player would only have one applicable primary image.