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
[email protected] www.nisc.coop
