Merge two rows if one of them contains a column that is null

Question

I have a table like this:

cusstomerID; EMail, Telefon, Fax
2; s@l.com; 0123; NULL      
2; s@l.com; NULL; 456       
3, p@y.com; 01792494976; NULL       
3; p@y.com; NULL; 01792494977
4; a@c.com; 0167578252; NULL        
5; qr@g.com; 069-541111; NULL   
5; qr@g.com; NULL; 069-541222   
6; cv@gv.com; NULL; 0123456

Expected Outcome:

cusstomerID; EMail, Telefon, Fax
2; s@l.com; 0123; 456
3, p@y.com; 01792494976; 01792494977
4; a@c.com; 0167578252; NULL        
5; qr@g.com; 069-541111; 069-541222 
6; cv@gv.com; NULL; 0123456

There are always 2 rows for every costumer in my data base when the customer has a telefon and a Fax Number (when the customer has only one of the 2 he/she has only one row): In the first row, the fax number is empty and in the second the telefone number is empty: how can I put it in one row? Via (my)SQL

I just want to update the table - not create a new one :)

Thanks for your support!

Isn't there any primary key in your table? – 1000111 Nov 14 '16 at 11:19 — 1000111, Nov 14 '16 at 11:19

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

To make it possible, you have to join the table to be able to choose the right field depending on value.

SELECT cusstomerID, EMail, IFNULL(t1.Telefon, t2.Telefon), IFNULL(t1.Fax, t2.Fax)
FROM tablename t1 
JOIN tablename t2 ON t1.cusstomerID = t2.cusstomerID

And to clean your table, you can do something like this :

UPDATE FROM tablename t1, tablename t2
SET t1.Telefon = IFNULL(t1.Telefon, t2.Telefon)
[...]
WHERE t1.cusstomerID = t2.cusstomerID ;

DELETE FROM tablename 
WHERE ( Telefon IS NULL
OR Fax IS NULL ) 
AND cusstomerID IN ( 
    SELECT cusstomerID 
    FROM tablename 
    GROUP BY cusstomerID 
    HAVING COUNT(cusstomerID ) = 2
);

Or you can create a new table and delete the older with a CREATE TABLE AS statement.

CREATE TABLE tablename2 AS ( SELECT t1.customerssID, t1.EMail, IFNULL( t1.Telefon, t2.Telefon ) AS Telefon, IFNULL( t1.Fax, t2.Fax ) AS Fax

FROM tablename t1 JOIN tablename t2 ON t1.customerssID = t2.customerssID );

Then you can DELETE the old table

DELETE tablename;

And rename the new one.

RENAME TABLE tablename2 tablename;

EDIT : use MAX with GROUP BY

After some research, I found this SO Post which makes jarlh answer correct. So the answer can be :

CREATE TABLE tablename2 AS ( SELECT `cusstomerID`, `EMail`, MAX(`Telefon`), MAX(`Fax`)
FROM tablename
GROUP BY `cusstomerID` );

Then you can DELETE the old table

DELETE tablename;

And rename the new one.

RENAME TABLE tablename2 tablename;

edited May 23 '17 at 10:32

Community

1
1

answered Nov 14 '16 at 11:22

JoDev

6,633
1
22
37

I just want to update the table - not create a new one :) – AbsoluteBeginner Nov 14 '16 at 11:24
1

`SELECT`won't create a new table... See basics of SQL to understand – JoDev Nov 14 '16 at 11:26
Ok- but how can I reinsert the new result into the existing table? – AbsoluteBeginner Nov 14 '16 at 11:28
But the data is in the same table: how can I join when there is only one table? – AbsoluteBeginner Nov 14 '16 at 11:34
Ok-I think my data set is too big to solve it this way (about 350k rows): Is there any faster way? – AbsoluteBeginner Nov 14 '16 at 11:47
You can do it in a script (in a programmation langage like PHP or PERL) – JoDev Nov 14 '16 at 11:48
This would definately exceed my knowledge :( – AbsoluteBeginner Nov 14 '16 at 11:50
Did you try the `CREATE TABLE ... AS` statement? I think it is the best way. – JoDev Nov 14 '16 at 11:53
it´s not working it always says: "Error Code: 1166. Incorrect column name 'IFNULL(t1.Telefon, t2.Telefon)' – AbsoluteBeginner Nov 14 '16 at 12:14
1

just abbreviate that column with your own choosed name – Emil Holub Nov 14 '16 at 12:16
I did-just changed it again for you – AbsoluteBeginner Nov 14 '16 at 12:41
seems like create table and IFNULL are not compatible – AbsoluteBeginner Nov 14 '16 at 12:47
@AbsoluteBeginner Look at the response of Emile Holub ... You have to alias columns in the `SELECT` to be able to create the table. I've corrected my answer accordingly. – JoDev Nov 14 '16 at 13:49

score 0 · Answer 2 · answered Nov 14 '16 at 13:48

update table1 t1,table1 t2 set t1.Telefon=t2.Telefon,t2.fax='delete' where t1.cusstomerID=t2.cusstomerID and t1.fax is not null and t2.Telefon is not null;

delete from table1 where fax='delete' and Telefon is not null;

Change the table name and try it.

Merge two rows if one of them contains a column that is null

2 Answers2