Hi !

With 2 identical tables, I can do this :

INSERT INTO table1 SELECT * FROM table2 WHERE...

Is there a way to do something similar with an UPDATE statement, to get the equivalent of

UPDATE table1 SET
field1=(SELECT field1 FROM table2 WHERE table1.id=table2.id),
field2=(SELECT field2 FROM table2 WHERE table1.id=table2.id),
field3=(SELECT field3 FROM table2 WHERE table1.id=table2.id),
...

?

TIA,

Heiko

Heiko,

<< Is there a way to do something similar with an UPDATE statement, to get
the equivalent of >>

Not currently, but such constructs will be possible when I (finally) get the
row values functionality added to EDB, which will hopefully be part of 2.03.
In such a case, you can then specify sets of columns in SET statements using
the row constructor syntax:

UPDATE table1 SET (Field1, Field2, Field3) =
(SELECT field1, field2, field3 FROM table2 WHERE table1.id=table2.id)

This why we left out the JOIN syntax in EDB - the idea has always been to
replace it with row values.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

sounds interesting. Just for understanding, would it then be possible to do something like
that:

UPDATE table1 SET * = (SELECT * FROM table2 WHERE table1.id=table2.id) ?

That would be really cool, the idea behind it is that you don't have to worry about future
alterations of the table structure, as long as both tables stay identical.

Heiko

Heiko,

<< sounds interesting. Just for understanding, would it then be possible to
do something like that: >>

I'd have to double-check the standard, but yes, I'm pretty sure that row
constructors allow for wildcards, just like with a normal SELECT statement.

This is one of the reasons why this has been put off for so long - it's a
rather pervasive change, albeit a very nice one.

--
Tim Young
Elevate Software
www.elevatesoft.com