Yao Feng wrote:
> There exist 2 tables in one database:log,url_ip,the TABLE log have one
> column:url varchar(100), and the TABLE url_ip have columns :url
> varchar(100) ,ip varchar(15) refelecting the mapping of ip of url.
> I want to replace the url in TABLE log with it's ip address using the
> second TABLE url_ip's values:
> UPDATE log set url=url_ip.ip where log.url=url_ip.url;
> but it failed. saying"fields url_ip.url not exist..."
> How can I do with it? any substitute?
You can't do that with one query, and you can't use more than one table in an update.
What you can do is:
1) create a temporary table 'tmpLog' with the same Fields as the 'log' table.
2) LOCK TABLES log WRITE, tmpLog WRITE
3) INSERT INTO tmpLog (field1, field2, ..., url) SELECT log.field1, log.field2, ...,
url_ip.ip FROM log, url_ip WHERE
4) DROP log
5) ALTER TABLE tmpLog RENAME log
6) UNLOCK TABLES