copy values from one row to next row

Question

I have a table in mysql having columns Description,Quantity , All Sizes

Description    quantity   Allsizes
Cap              3           small
0                3           medium
0                3           large
Shirt            2           small
0                2           medium

I want to replace the '0' in description column to the value of the row preceeding it, In this example the o/p would be

Cap              3            small
Cap              3            medium
Cap              3            large
Shirt            2            small
Shirt            2            medium

How can i achieve this ?Any help is much appreciated

Please refer to this [answer](http://stackoverflow.com/questions/1387692/mysql-copy-value-from-one-row-to-another) — user3337714, Jul 07 '15 at 09:57
MySQL tables aren't ordered. Is there another column that can be used to order them? — Barmar, Jul 07 '15 at 09:57
@user3337714 It's not the same, that question has a column that can be used to link the rows. — Barmar, Jul 07 '15 at 09:58
I am adding another answer. This is the typical case. [New Answer](http://stackoverflow.com/questions/5574434/update-row-with-data-from-another-row-in-the-same-table) — user3337714, Jul 07 '15 at 09:58
That question also has a column that links the rows that need to be filled in with the one that should be copied from. — Barmar, Jul 07 '15 at 10:01
@barmar i can add a ID column and set it to auto increment to set an order — Mk5352, Jul 07 '15 at 10:08

Vishal JAIN · Accepted Answer · 2015-07-08T05:57:19.667

0

Try below query just verify your table and column name Demo:

 select 
            e.id,
            CASE e.description WHEN '0'
            Then
              @prev
            else
              e.description
            END as description,

            CASE e.description WHEN '0'
            THEN
              @prev := @prev    
            ELSE
              @prev := e.description 
            END as current,
            e.quantity as quantity,
            e.Allsizes
        from
            (
                select
                    @prev := ''
            ) as i,
            table_name as e
        order by
            e.id

edited Jul 08 '15 at 05:57

answered Jul 07 '15 at 10:22

Vishal JAIN

1,940
1
11
14

i tried the code but the output in wamp server is blob for replace value and current. – Mk5352 Jul 08 '15 at 05:36
I think you need to set initial value of @prev empty string i.e ' ' instead of null. Changed in above query. Check it. – Vishal JAIN Jul 08 '15 at 05:57

copy values from one row to next row

1 Answers1