CODEWITHSUNDEEP
mysqlsql
aizaz
aizaz

Reputation: 3062

Query to replace null values from the table

I have table like this,

Current Table1 

id | car_name | model | year
---+----------+-------+----- 
1  |a         | abc   | 2000
2  |b         | xyx   | 2001
3  |null      | asd   | 2003
4  |c         | qwe   | 2004
5  |null      | xds   | 2005
6  |d         | asd   | 2006

as you can see i have some null values , i want to replace them with the previous row values.

So it should become like this

Desired Table1 

id | car_name | model | year
---+----------+-------+----- 
1  |a         | abc   | 2000
2  |b         | xyx   | 2001
3  |b         | asd   | 2003
4  |c         | qwe   | 2004
5  |c         | xds   | 2005
6  |d         | asd   | 2006

I googled, but didn't find any solution for this.

Any suggestions always welcome.

Thanks in advance.

Upvotes: 2

Views: 749

Answers (3)

Justin
Justin

Reputation: 9724

This query should work even if there are several records in a row with NULL

Query:

SQLFIDDLEExample

UPDATE Table1
SET car_name = (SELECT t1.car_name
                FROM (SELECT * FROM Table1) t1
                WHERE t1.id < Table1.id
                AND t1.car_name is not null
                ORDER BY t1.id DESC
                LIMIT 1)
WHERE car_name is null

Result:

| ID | CAR_NAME | MODEL | YEAR |
--------------------------------
|  1 |        a |   abc | 2000 |
|  2 |        b |   xyx | 2001 |
|  3 |        b |   asd | 2003 |
|  4 |        c |   qwe | 2004 |
|  5 |        c |   xds | 2005 |
|  6 |        d |   asd | 2006 |

Upvotes: 3

Ankit Bansal
Ankit Bansal

Reputation: 5082

It might be better to do it via a script/program or even a stored proc rather than doing it plainly using sql since you get additional benefits of handling some of the corner cases (like what happens when there are two successive rows with null etc) and also since I assume its a one time job.

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Try something like this:

UPDATE Table1 as first
INNER JOIN Table1 as second
    ON second.id = first.id-1
SET first.car_name = second.car_name
WHERE first.car_name IS NULL

Upvotes: 2

Related Questions