UPDATE Only one row (using update and join)

I'm trying to get a fast, simple sql query to update only one row at a time while using join too.

I have tried LIMIT, but to no success.

Query:

UPDATE
table1
JOIN
table2 ON table2.col=table1.col
SET 
table1.row1='a value'
WHERE
table1.row2 LIKE '%something%' 
LIMIT 1

Message:


Error Code: 1221. Incorrect usage of UPDATE and LIMIT

LIMIT is not allowed when the UPDATE has joins. You can however move the join and the LIMIT inside a subquery and then join back to the table to be updated using the primary or a unique key. This is allowed:

UPDATE table1 AS upd
  JOIN
    ( SELECT t1.pk
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%' 
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
    ON sel.pk = upd.pk
SET 
   upd.row1 = 'a value' ;

It's also good to use ORDER BY with LIMIT. Otherwise an arbitrary row will be selected.

If you want to update both tables (one row from each one), then you just need to join the derived table to the second table, too:

UPDATE 
    ( SELECT t1.pk AS pk1,                     -- The PK columns 
             t2.pk AS pk2                      -- of each table
      FROM table1 AS t1
        JOIN
           table2 AS t2 ON t2.col = t1.col
      WHERE t1.row2 LIKE '%something%' 
      -- ORDER BY some_expressions
      LIMIT 1
    ) AS sel
  JOIN table1 AS upd1  ON sel.pk1 = upd1.pk    -- join table1    
  JOIN table2 AS upd2  ON sel.pk2 = upd2.pk    -- join table2
SET 
   upd1.row1 = 'a value',
   upd2.roww = 'some other value' ;

Test at rextester.com

StackExchange Database Administrators Q#166716, answer score: 7