logo

Removing Null Values and Data Integrity

   

Added on  2019-09-23

7 Pages661 Words100 Views
 | 
 | 
 | 
Contents1st Problem: & Solution................................................................................................................................22nd Problem: & Solution..............................................................................................................................7
Removing Null Values and Data Integrity_1

1st Problem: & SolutionNull values are presented in following tables.Table Names: Flight, Planes (speed)Table: PlanesWe can see the column “speed” in table Planes have null values. So to remove this problem I simply follow these steps:1-I ran this query to findout those rows which have only null value in speed column;Select * from planes where speed is null;2-Now I got a output with null values. Then I exported as .csv file and then I just gave some values in “speed” column own my own as you can see in the following screen shot:
Removing Null Values and Data Integrity_2

3-Then I create a temporary table and then load this file again to that temp file same like this:CREATE TABLE temp_update_planes (tailnum char(6),year integer,type varchar(50),manufacturer varchar(50),model varchar(50),engines integer,seats integer,speed integer,engine varchar(50));LOAD DATA INFILE 'C:/Users/PC/Desktop/Today/flights/planes_output.csv'INTO TABLE temp_update_planesFIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 ROWS(tailnum, @year, type, manufacturer, model, engines, seats, @speed, engine)SETyear = nullif(@year,''),speed = nullif(@speed,'');4-And then I just simply apply the inner join query to update the existing table “Planes” with new value for “speed” column. UPDATE planesINNER JOIN temp_update_planes ontemp_update_planes.tailnum = planes.tailnum andtemp_update_planes.year = planes.year andtemp_update_planes.type = planes.type and
Removing Null Values and Data Integrity_3

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents