คำสั่ง SQL

set @annual1 := 2562 ;

DELETE from land where annual != @annual1 ;

delete from lu where land_id not in 

(select DISTINCT land_id from land order by land_id) ;

DELETE from building where annual != @annual1 ;

delete from bu where bid not in 

(select DISTINCT bid from building order by bid) ;

delete from b_img where bid not in 

(select DISTINCT bid from building order by bid) ;

DELETE from sign_board where annual != @annual1 ;

delete from s_img where s_id not in 

(select DISTINCT s_id from sign_board order by s_id) ;

// ตรวจสอบชนิดของ เอกสารสิทธิ์ 

SElect l1.ldoc_type,l2.ldoc_name,count(*) total from land l1

LEFT JOIN l_type l2 on l1.ldoc_type=l2.ldoc_type

group  by l1.ldoc_type ;

// สิ่งก่อสร้าง 

SElect annual,count(*) total from building

group  by annual ;

// ตรวจสอบ BT_ID 

SElect b1.bt_id,b2.bt_name,count(*) total from building b1

LEFT JOIN b_type b2 on b1.bt_id = b2.bt_id

group  by bt_id ;

// แก้ BT_ID =1 

update building set bt_id =1 where bt_id is null ;

// ตรวจสอบ bm_id

SElect bm_id,count(*) total from building

group  by bm_id ;

// แก้ bm_id =1 

update building set bm_id =1 where bm_id is null ;

// เช็ค PARCEL land 

select *  from land 

where parcel_code is NULL

or parcel_code ='' ; 

//  ลบ 

delete  from land 

where parcel_code is NULL

or parcel_code ='' ; 

// ใส่ค่า  land_id

update building b1

LEFT JOIN (select DISTINCT parcel_code,land_id from land ORDER BY parcel_code) b2

on b1.parcel_code =b2.parcel_code  

set b1.land_id = b2.land_id ; 

// LAND_id วา่ง  building

select *  from building where LAND_id is null ;

// ลบ LAND_id วา่ง  building

delete from building where LAND_id is null ;

// ตรวจสอบ BUILDING น่าจะผิด  

select *  from building 

where parcel_code is NULL or parcel_code ='' 

or land_id is null  or owner_id is null

or bt_id is null  or bm_id is null 

or (substr(parcel_code,3,1) not between 'A' and 'Z');

// ลบถ้าไม่แก้ building 

delete  from building 

where parcel_code is NULL or parcel_code ='' 

or land_id is null  or owner_id is null

or bt_id is null  or bm_id is null 

or (substr(parcel_code,3,1) not between 'A' and 'Z');

// ลบอีกรอบ 

DELETE from land where annual != @annual1 ;

delete from lu where land_id not in 

(select DISTINCT land_id from land order by land_id) ;

DELETE from building where annual != @annual1 ;

delete from bu where bid not in 

(select DISTINCT bid from building order by bid) ;

delete from b_img where bid not in 

(select DISTINCT bid from building order by bid) ;

DELETE from sign_board where annual != @annual1 ;

delete from s_img where s_id not in 

(select DISTINCT s_id from sign_board order by s_id) ;

// ตรวจสอบล่าสุด 

select a.annual,(select count(*)   from owner) t_owner,

a.t_land,b.t_building,c.t_sign_board

from   

(select annual,count(*) t_land from land ) a  

LEFT JOIN

(select annual,count(*) t_building from building ) b on a.annual =  b.annual

LEFT JOIN

(select annual,count(*) t_sign_board from sign_board) c  on a.annual =  c.annual ;