MySql combining update with an inner join and limit

Question

I'm trying to update a row in table amga with just one row from table amgb joined by their itemTempId.

My problem is that, there may be upto 6 rows in table amgb for that itemTempIdand I need to use only one from it for the update.

I'm familiar with doing updates with joins, but when I added a Limit (so as to get just one row) I get the error message Incorrect usage of update and limit. I read that this is not possible, but would there be another way to do this?

amga

"id"    "itemId"    "itemTempId"    "itemImageName" "itemName"                  "itemCountry"   "userId"
"1"     "US1"       "T001"          \N              "Samsung Galaxy Note 5"     "US"            "1"
"2"     "CA2"       "T002"          \N              "Samsung Galaxy Note 6"     "CA"            "2"
"3"     "UK3"       "T003"          \N              "Samsung Galaxy Note 7"     "UK"            "3"

amgb

"id"    "itemId"    "itemTempId"    "itemImageName"     "userId"
"1"     "US1"       "T001"          "front.jpg"         "1"
"2"     "US1"       "T001"          "side-left.jpg"     "1"
"3"     "US1"       "T001"          "side-right.jpg"    "1"
"4"     "US1"       "T001"          "back.jpg"          "1"
"5"     "CA2"       "T002"          "front.jpg"         "2"
"6"     "CA2"       "T002"          "side-left.jpg"     "2"
"7"     "CA2"       "T002"          "side-right.jpg"    "2"
"8"     "CA2"       "T002"          "back.jpg"          "2"
"9"     "UK3"       "T003"          "front.jpg"         "3" 

Sql I used

update amga a inner join amgb b on a.itemTempId = b.itemTempId 
set a.itemImageName = b.itemImageName where a.itemTempId = 'T001' limit 1;

Expected results: Table amga after update

 "id"   "itemId"    "itemTempId"    "itemImageName" "itemName"                  "itemCountry"   "userId"
    "1"     "US1"       "T001"      front.jpg       "Samsung Galaxy Note 5"     "US"            "1"
    "2"     "CA2"       "T002"      \N              "Samsung Galaxy Note 6"     "CA"            "2"
    "3"     "UK3"       "T003"      \N              "Samsung Galaxy Note 7"     "UK"            "3"

Note: itemTempId is updated with front.jpg, which is the first row for itemTempId = T001 in amgb

Any help appreciated.

Update I noticed it works if I remove the limit, and that it updates too. But is it the right way to do it? What does MySql do with the other rows in the select?

update amga a inner join amgb b on a.itemTempId = b.itemTempId 
set a.itemImageName = b.itemImageName where a.itemTempId = 'T001';

Answer 1

Maybe you can use a subquery:

UPDATE amga a
SET a.itemImageName =
  (SELECT b.itemImageName
   FROM amgb b
   WHERE b.itemTempId = 'T001'
   ORDER BY b.id LIMIT 1)
WHERE a.itemTempId = 'T001'