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


ftp_login     VARCHAR(255)                      latin1_swedish_ci


ftp_password  VARCHAR(255)                      latin1_swedish_ci


ftp_mode      ENUM('Production','Test','Both')  latin1_swedish_ci  YES


ftp_passive   ENUM('Normal','Passive')          latin1_swedish_ci  YES


url_server    VARCHAR(255)                      latin1_swedish_ci


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 


Query QuestionDirk Bremer1 Feb