Mysql – UPDATE Only one row (using update and join)

join;limitsMySQLupdate

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

Best Answer

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

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.