How to update by Ignoring null values in mysql-CodePudding

I have my data like this,

Heading

& I want output in

Name Gender Salary Sam M 3.45 Priya F 4.02

Please help me out. thank you.

CodePudding user response：

This is not a practical scenario. At least you must have a Order by column or a sequence number to manage your data set.  but try something like this. Most probably, **Order by** cause you many issues wile you working with the real data set.


CREATE TEMPORARY TABLE t_names
SELECT ROW_NUMBER() OVER ( ORDER BY NAME) rowid, NAME FROM Heading 
WHERE NAME IS NOT NULL;


CREATE TEMPORARY TABLE t_gender
SELECT ROW_NUMBER() OVER ( ORDER BY NAME) rowid, gender FROM Heading 
WHERE gender IS NOT NULL;


CREATE TEMPORARY TABLE t_salary
SELECT ROW_NUMBER() OVER ( ORDER BY NAME) rowid, salary FROM Heading 
WHERE salary IS NOT NULL;

SELECT nm.name, tg.gender, sl.salary FROM t_names nm
  INNER JOIN t_gender tg ON tg.rowid = nm.rowid
  INNER JOIN  t_salary sl ON sl.rowid = nm.rowid

what i given you is a suggestion for your scenario. But if this is a real world scenario, better to discuss this with your team and bring up a good structure for the table with Primary key, sequence number for the table.

at least add a new column as and set it as a identity column, and then it will create a sequence for your table.

if you created a sequence number as i explained you, then there is a 99% possibility to retrieve your exact result set

CREATE TEMPORARY TABLE t_names
SELECT ROW_NUMBER() OVER ( ORDER BY seq) rowid, NAME FROM Heading 
WHERE NAME IS NOT NULL;


CREATE TEMPORARY TABLE t_gender
SELECT ROW_NUMBER() OVER ( ORDER BY seq) rowid, gender FROM Heading 
WHERE gender IS NOT NULL;


CREATE TEMPORARY TABLE t_salary
SELECT ROW_NUMBER() OVER ( ORDER BY seq) rowid, salary FROM Heading 
WHERE salary IS NOT NULL;

SELECT nm.name, tg.gender, sl.salary FROM t_names nm
  INNER JOIN t_gender tg ON tg.rowid = nm.rowid
  INNER JOIN  t_salary sl ON sl.rowid = nm.rowid

CodePudding user response：

I don't know if you want to Update or you just want to Select your table.

But if you only want to Select the data that are not NULL values you can:

Select name, gender, salary(L) from table_name where 
name is not null and gender is not null and salary(L) is not null