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, Sep 11 '14 at 06:11
And for `T002` (if `T002` is used in the update) it'll be `5` — Norman, Sep 11 '14 at 06: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](http://stackoverflow.com/questions/3333665/mysql-rank-function). — Josien, Sep 11 '14 at 06:21

Yigitalp Ertem · Answer 1 · 2014-09-11T06:40:19.527

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 '14 at 06:40

answered Sep 11 '14 at 06:19

Yigitalp Ertem

1,901
24
27

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.) ` – Norman Sep 11 '14 at 07:08
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: http://stackoverflow.com/questions/16473402/update-multiple-columns-from-subquery – Yigitalp Ertem Sep 11 '14 at 07:29
I'll try. Im using your first answer. Thanks for that. – Norman Sep 11 '14 at 07:37

MySql combining update with an inner join and limit

1 Answers1