Please note, this is a STATIC archive of website www.tutorialspoint.com from 11 May 2019, cach3.com does not collect or store any user information, there is no "phishing" involved.
Tutorialspoint

SQL Statement Generator for Merging Project Tables

# Hello World program in Python
colnames ="""
Project_ID
Project_Name
County
District
Route
Project_Date
Back_Prefix
Back
Ahead_Prefix
Ahead
PM_KP_U
Comments
ModifiedBY
CreatedBY
Modified
tracking_office
tracking_year
tracking_record
status
rejection_comment
reference_number
contact_email
special_instructions
uploaded_date
Loc
create_date
"""
names = colnames.split()
ltable = "geo_projects"
rtable = "geo_projects_20190313_craig"
#print names
for n in names:  
    print "UPDATE " + ltable + " p"
    print "INNER JOIN " + rtable + " pc ON p.ProjectKey = pc.ProjectKey"
    print "SET p." + n + " = pc." + n
    print "WHERE pc." + n + " IS NOT NULL"
    print "  AND TRIM(pc."+n+") != ''"
    print "  AND pc." + n + " != '0000-00-00'"
    print "  AND pc." + n + " != '0000-00-00 00:00:00'"
    print "  AND ( p." + n + " IS NULL OR p." + n + " != pc." + n + " );"
    print
    print "UPDATE " + ltable + " p"
    print "INNER JOIN " + rtable + " pc ON p.ProjectKey = pc.ProjectKey"
    print "SET p." + n + " = NULL"
    print "WHERE (  pc." + n + " IS NULL"
    print "       OR TRIM(pc." + n + ") = ''"
    print "       OR pc." + n + " = '0000-00-00'"
    print "       OR pc." + n + " = '0000-00-00 00:00:00' )"
    print "  AND p." + n + " IS NOT NULL;"
    print
    print "SELECT p." + n + " p_, pc." + n + " pc_"
    print "FROM " + ltable + " p"
    print "INNER JOIN " + rtable + " pc ON p.ProjectKey = pc.ProjectKey;"
    print "-- " + n + " done ----"
    print

Advertisements
Loading...

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.