From: Christian Mack
Date: March 19 1999 1:57pm
Subject: Re: How to rielize such func.?
List-Archive: http://lists.mysql.com/mysql/602
Message-Id: <36F257D6.A79AC1F3@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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?

Hi Yao

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
log.url=url_ip.url
4) DROP log
5) ALTER TABLE tmpLog RENAME log
6) UNLOCK TABLES

Tschau
Christian