search

Home  >  Q&A  >  body text

Update the id of the second row based on the first id

My original data is as follows:

sid id  amount
1   12  30
2   45  30
3   45  50
4   78  80
5   78  70

The desired output is as follows:

sid id  amount
1   12  30
2   45  30
3   45  30
4   78  80
5   78  80

The purpose is to get the amount of the first occurrence of id and update the amount when it appears the second time I'm trying the following code:

UPDATE foo AS f1
  JOIN
  ( SELECT cur.sl, cur.id,
           cur.amount AS balance 
    FROM foo AS cur
      JOIN foo AS prev
        ON prev.id = cur.id
    GROUP BY cur.tstamp
  ) AS p
  ON p.id = a.id
SET a.amount = p.amount ;

P粉818088880P粉818088880392 days ago487

reply all(1)I'll reply

  • P粉904450959

    P粉9044509592024-01-29 20:23:49

    Join the table to a query that returns the minimum value sid for each id and returns itself again to get the value with that minimum sid## Rows of #:

    UPDATE tablename t1
    INNER JOIN (
      SELECT MIN(sid) sid, id
      FROM tablename
      GROUP BY id
    ) t2 ON t2.id = t1.id AND t2.sid < t1.sid
    INNER JOIN tablename t3 ON t3.sid = t2.sid
    SET t1.amount = t3.amount;

    View

    Demo.

    For MySql 8.0, if you use the

    ROW_NUMBER() window function, you only need 1 connection to complete:

    UPDATE tablename t1
    INNER JOIN (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY sid) rn
      FROM tablename
    ) t2 ON t2.id = t1.id  
    SET t1.amount = t2.amount
    WHERE t2.rn = 1;

    View

    Demo.

    reply
    0
  • Cancelreply