Replace NULL values in query with values of another row

Question

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

Could a CASE statement help you?

Ben
– Ben

2017-05-12 14:38:49 +00:00
Commented May 12, 2017 at 14:38 — Ben
– Ben, Commented May 12, 2017 at 14:38
I don't know, that's the reason I asked ;)

bricklore
– bricklore

2017-05-12 14:39:26 +00:00
Commented May 12, 2017 at 14:39 — bricklore
– bricklore, Commented May 12, 2017 at 14:39

Gordon Linoff · Accepted Answer · 2017-05-12 14:39:35Z

4

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;

answered May 12, 2017 at 14:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ayusha · Accepted Answer · 2017-05-12 14:57:08Z

1

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;

edited May 12, 2017 at 14:57

answered May 12, 2017 at 14:53

ayusha

4844 silver badges12 bronze badges

1 Comment

Lajos Arpad Over a year ago

As far as I know isnull does not exist for MySQL. It is an MS SQL function.

Lajos Arpad · Accepted Answer · 2017-05-12 14:54:39Z

0

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.

answered May 12, 2017 at 14:54

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

Collectives™ on Stack Overflow

Replace NULL values in query with values of another row

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related