CODEWITHSUNDEEP
mysqlsqljoinself-join
bricklore
bricklore

Reputation: 4165

Replace NULL values in query with values of another row

How can I replace all null values in the result set with values from another row of the same table? (like a fallback query)

Example schema:

CREATE TABLE parent (
    id INTEGER NOT NULL AUTO_INCREMENT,
    int1 INTEGER,
    int2 INTEGER,
    int3 INTEGER,
    PRIMARY KEY (id)
)

The query:

SELECT * FROM table1
WHERE id = ?

But I need to replace all null values with those of another row. I am looking for something like this:

SELECT * FROM table1 WHERE id = ?
   REPLACE ALL NULL VALUES WITH (
       SELECT * FROM table1 WHERE id = ?
   )

Example:

id    int1    int2   int3
---------------------------
 1     1      null    1
 2     null   null    1
 3     1       4      0

When I query for id 1 first and id 3 as fallback, I expect the result to be:

id    int1   int2   int3
---------------------------
 1     1      4      1

Upvotes: 2

Views: 3339

Answers (3)

ayusha
ayusha

Reputation: 484

join and ISNULL() (for MS SQL and IFNULL for MySql) function will be helpful in this case:

select t1.id, ISNULL(main.int1, fallback.int1) as int1,
       ISNULL(main.int2, fallback.int2) as int2,
       ISNULL(main.int3, fallback.int3) as int3
from table1 as main join table1 as fallback on fallback.id = 3
where main.id = 1;

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76434

Take a look at case.

select case mycolumn is null
       when 1 then myothercolumn
       else mycolumn
       end
from mytable

You can also embed case-when into another. This should be sufficient for you to solve the problem.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this using join and coalesce():

select t1.id,
       coalesce(t1.int1, tt1.int1) as int1,
       coalesce(t1.int2, tt1.int2) as int2,
       coalesce(t1.int3, tt1.int3) as int3
from table1 t1 join
     table1 tt1
     on tt1.id = 3
where t1.id = 1;

Upvotes: 4

Related Questions