logo

How to Remove Null Values in MySQL Tables: Desklib Solutions

7 Pages661 Words100 Views
   

Added on  2019-09-23

About This Document

This article provides solutions to two common problems in MySQL tables: removing null values and fixing data inconsistencies. The first problem involves removing null values from the 'speed' column in the 'planes' table. The second problem involves fixing data inconsistencies between the 'planes' and 'flights' tables. The article provides step-by-step instructions on how to solve these problems using MySQL queries and temporary tables.

How to Remove Null Values in MySQL Tables: Desklib Solutions

   Added on 2019-09-23

ShareRelated Documents
Contents1st Problem: & Solution................................................................................................................................22nd Problem: & Solution..............................................................................................................................7
How to Remove Null Values in MySQL Tables: Desklib Solutions_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:
How to Remove Null Values in MySQL Tables: Desklib Solutions_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
How to Remove Null Values in MySQL Tables: Desklib Solutions_3

End of preview

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

Related Documents
Home Inventory Database: Software Tool Description, SQL Section, Security Measures, Testing and Validation Strategies
|7
|800
|134

Structured Query Language - PDF
|9
|1603
|187

The Operations Rejected by the System
|7
|1034
|382