Query Question

Query Question

am 01.02.2010 15:48:11 von Dirk Bremer

------_=_NextPart_001_01CAA34D.93D2570D
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=20

For the given table:

=20

=20

FIELD TYPE COLLATION NULL
KEY DEFAULT Extra PRIVILEGES COMMENT

=20

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

=20

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

=20

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

=20

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

=20

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

=20

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

=20

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

=20

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

=20

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

=20

=20

=20

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

=20

=20

=20

"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"

=20

=20

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.

=20

Dirk Bremer - Senior Systems Engineer - Utility - AMS

NISC Lake St. Louis MO - USA Central Time Zone

636-755-2652 fax 636-755-2502=20

dirk.bremer@nisc.coop www.nisc.coop


------_=_NextPart_001_01CAA34D.93D2570D--