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';

How are you defining "the first row" when joining? I suppose it is not a random row, is it the row with the lowest id? (As there is no guaranteed order of rows in a table.) — Josien
– Josien, Commented Sep 11, 2014 at 6:11
And for T002 (if T002 is used in the update) it'll be 5 — Norman
– Norman, Commented Sep 11, 2014 at 6:14
In MS SQL I would solve this with a ranking function, so you first do a ranking on amgb and then join every row with the corresponding row ranked 1. Seems something along those lines could be done in MySQL too: Mysql rank function. — Josien
– Josien, Commented Sep 11, 2014 at 6:21

Yigitalp Ertem · Accepted Answer · 2014-09-11 06:40:19Z

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'

edited Sep 11, 2014 at 6:40

answered Sep 11, 2014 at 6:19

Yigitalp Ertem

2,06926 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Norman Over a year ago

In your sql, is it possible to update two columns with data from the select Eg: SET.itemImageName, SET itemImagePath = (select b.itemImageName, b.itemImagePath etc.)

Yigitalp Ertem Over a year ago

Afaik, unfortunately not. But you can try the following update statement with a join. I'm not adding it to the answer since I'm not sure. You may format and try it: UPDATE amga a INNER JOIN (SELECT b.itemImageName, b.itemImagePath FROM amgb b WHERE b.itemTempId = 'T001' ORDER BY b.id LIMIT 1) c ON c.itemTempId = a.itemTempId SET a.itemImageName = c.itemImageName, a.itemImagePath = b.itemImagePath WHERE a.itemTempId = 'T001'; I checked out this answer: stackoverflow.com/questions/16473402/…

Norman Over a year ago

I'll try. Im using your first answer. Thanks for that.

Collectives™ on Stack Overflow

MySql combining update with an inner join and limit

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related