PHP Versions Question posted in
You can find a list of all versions here.

Php versions – How to overwrite next rows using UPDATE?

I have this MYSQL table (simplified version) with some erased ids:

+-----+-------+---------+
| id  | name  | country |
+-----+-------+---------+
| 1   | John  | England |
| 5   | Emily |   USA   |
| 9   |  Joe  |   USA   |
| 11  |Michael|   USA   |
| 13  | Liam  |   USA   |
+-----+-------+---------+

How do I overwrite each row from "John" using php? (last name "Liam" gets kicked out)

I want it to look like this:

+-----+-------+---------+
| id  | name  | country |
+-----+-------+---------+
| 1   | John  | England |
| 5   | John  |   USA   |
| 9   | Emily |   USA   |
| 11  |  Joe  |   USA   |
| 13  |Michael|   USA   |
+-----+-------+---------+
Tags:

2

Answers


  1. 0

    I understand that you want to copy each name on the "next" row.

    If you are running MySQL 8.0, you can do this with a self join and window functions:

    update mytable t
inner join (
    select id, lag(name) over(order by id) as lag_name
    from mytable
) t1 on t1.id = t.id
set t.name = t1.lag_name
where t1.lag_name is not null
    Login or Signup to reply.
  2. 0

    You can use correlated query as follows:

    Update your_table t
   Set t.name = 
       Coalesce(Select ttt.name from your_table ttt 
                 where ttt.id = (Select max(tt.id) from your_table tt
                                 Where tt.id < t.id))
              , t.name)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search