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.
