Merge rows code returns empty content

Question

I am merging two columns in the same table. I am following the answer in this thread.

I have a table that looks like this and the var as the merged variable (it has an id but not included here):

var1    var2    var
1               1
        2       2
8               8    
        10      10

I use the code below to get all values for the concerned columns which works perfectly. The two concerned columns are combined into one with their respective values.

# code1
SELECT ID, "var1" AS var, var1 AS val FROM source
UNION ALL
SELECT ID, "var2" AS var, var2 AS val FROM source

Next is I need to get the unique values per row and column combination:

# code2
SELECT ID, First(val) AS FirstOfVal
FROM (
    SELECT DISTINCT ID, val
    FROM code1
    WHERE val IS NOT NULL
  )
GROUP BY ID
HAVING COUNT(*) = 1

In code2 I expect to get a table with a FirstOfVal column filled with values but no contents in my case.

How do I solve this?

I tried using it on other combinations of columns and it worked perfectly except for this column that I'm modifying. The only difference I can think of these columns I'm having a problem merging is that they contain a lot of empty rows (as seen above) and each column has its own value, no two rows' columns have both values.

MySQL doesn't have `FIRST()`. You should be getting an error from code2. — Barmar, Dec 31 '13 at 06:35

score 0 · Accepted Answer · answered Dec 31 '13 at 08:07

Okay I finally solved it!

Here are the steps for this special case:

SELECT the id and the desired columns. Note that this expects the columns to both not contain values (I haven't tested it with both columns containing a value, whether the same or not).
```
# query1
SELECT id, col1 & col2 AS merged
FROM table_name;
```
Insert the results of the query before into a temporary table for security (better save it that encounter errors).
```
# query2
SELECT * INTO temp
FROM query1
WHERE FirstOfVal;
```

Insert the contents of the temporary table into the main table.

UPDATE table_name
    INNER JOIN temp ON table_name.id = temp.id
    SET table_name.merged = [temp].[merged];

You now have the merged columns into your source table.

Merge rows code returns empty content

1 Answers1

Linked