List:General Discussion« Previous MessageNext Message »
From:Dirk Bremer Date:February 1 2010 2:48pm
Subject:Query Question
View as plain text  
 

For the given table:

 

 

FIELD         TYPE                              COLLATION          NULL
KEY     DEFAULT  Extra   PRIVILEGES                       COMMENT

 

------------  --------------------------------  -----------------
------  ------  -------  ------  -------------------------------
-------

 

job_coop      VARCHAR(6)                        latin1_swedish_ci
PRI                      SELECT,INSERT,UPDATE,REFERENCES        

 

ftp_server    VARCHAR(255)                      latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES        

 

ftp_login     VARCHAR(255)                      latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES        

 

ftp_password  VARCHAR(255)                      latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES        

 

ftp_mode      ENUM('Production','Test','Both')  latin1_swedish_ci  YES
(NULL)           SELECT,INSERT,UPDATE,REFERENCES        

 

ftp_passive   ENUM('Normal','Passive')          latin1_swedish_ci  YES
(NULL)           SELECT,INSERT,UPDATE,REFERENCES        

 

url_server    VARCHAR(255)                      latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES        

 

url_port      INT(11)                           NULL
0                SELECT,INSERT,UPDATE,REFERENCES         

 

 

 

I have situations where different rows have the same value in the
ftp_server column:

 

 

 

"job_coop"  "ftp_server"                 "ftp_login"    "ftp_password"
"ftp_mode"    "ftp_passive"    "url_server"    "url_port"

"B15027"    "15027dbs.nisc.lan"    "mailroom"    "ca15027"
"Both"          "Normal"         "15027dbs"    "35000"

"B15127"    "15027dbs.nisc.lan"    "mailroom"    "ca15027"
"Both"          "Normal"         ""                  "0"

"B15227"    "15027dbs.nisc.lan"    "mailroom"    "ca15027"
"Test"          "Normal"          ""                 "0"

 

 

I'd like to update the url_server and url_port fields in this example
for B15127 and B15227 to the values contained in B15027. There are other
examples as well. I would like a query that would update all instances
where the ftp_server values matched and where the url_server and
url_port have no assigned values and they would be updated from the
matching ftp_server that did have values in the url_server and url_port.
I imagine that this might require a join and perhaps a temporary table.
Please advise.

 

Dirk Bremer - Senior Systems Engineer - Utility - AMS

NISC Lake St. Louis MO - USA Central Time Zone

636-755-2652 fax 636-755-2502 

dirk.bremer@stripped www.nisc.coop


Thread
Query QuestionDirk Bremer1 Feb