List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 19 1999 1:57pm
Subject:Re: How to rielize such func.?
View as plain text  
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

Thread
How to rielize such func.?Yao Feng19 Mar
  • Re: How to rielize such func.?Christian Mack19 Mar