List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 8 1999 7:06pm
Subject:Re: update a table from another table ?
View as plain text  
FRed wrote:
> 
> Hi,
> 
> I would like to update a column from an another table. something like
> update table1 as 1, table2 as 2 where 1.ID=2.FRG_KEY set 1.COL=2.OTHER_COL;
> 
> But no way, I did not find any clue in the Manual. Does anyone could help me ?
> 
> Thank you
> 
>            Frédéric Fillon

Hi Frederic

You can't do this directly (yet).

But you can create a temporary table with all fields like table1.
Then: 
INSERT INTO 
	tmp_table
	( ID
	, NAME
	, ...
	, COL)
SELECT
	a.id
	, a.name
	, a. ...
	, IFNULL( b.OTHER_COL, a.COL )
FROM
	table1 AS a
	LEFT JOIN table2 AS b ON a.ID = b.FRG_KEY
	;
LOCK TABLES table1 WRITE, tmp_table WRITE;
FLUSH TABLES;
DROP TABLE table1;
ALTER TABLE tmp_table RENAME table1;
FLUSH TABLES;
UNLOCK TABLES;

Tschau
Christian

PS: Sorry for the late answer, I was really busy.

Thread
update a table from another table ?FRed24 Aug
  • Re: update a table from another table ?Christian Mack8 Sep