I have finally worked out the syntax for updating a single column in
a table in one database from a column in a table in another
database...

So is it possible and if so what is the syntax to update 2 or more
columns in a single statement?

Or am I stuck with a statement per column?

... set (cola, colb) = select (colx, colz ....)

Malcolm
--

Malcolm

> So is it possible and if so what is the syntax to update 2 or more
> columns in a single statement?

That should be no problem: I use this code in a procedure e.g.:

Prepare UpdateStatement from
      'update Statistik2C s set
      M1 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 1),
      M2 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 2),
      M3 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 3),
      M4 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 4),
      M5 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 5),
      M6 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 6),
      M7 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 7),
      M8 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 8),
      M9 = (select Alle from temp t where t.jahr = s.jahr and t.monat = 9),
      M10 = (select Alle from temp t where t.jahr = s.jahr and t.monat
= 10),
      M11= (select Alle from temp t where t.jahr = s.jahr and t.monat =
11),
      M12 = (select Alle from temp t where t.jahr = s.jahr and t.monat
= 12)';
  EXECUTE UpdateStatement;

Uli

Malcolm,

> So is it possible and if so what is the syntax to update 2 or more
> columns in a single statement?

It's possible using one subquery for each column you want to update, as Uli
indicated.

> .. set (cola, colb) = select (colx, colz ....)

Currently EDB doesn't support ROW values, but it will in version 2.03.
I don't know if Tim is going to change the INSERT statement syntax in the
initial 2.03 build but if that is the case then we will be able to use that
kind of set clause.
Let's wait and see what Tim has to say about this .

--
Fernando Dias
[Team Elevate]

Fernando,

<< Currently EDB doesn't support ROW values, but it will in version 2.03. I
don't know if Tim is going to change the INSERT statement syntax in the
initial 2.03 build but if that is the case then we will be able to use that
kind of set clause. >>

With ROW value support, the UPDATE syntax will allow multiple column
assignments in one shot:

UPDATE MyTable
SET (MyColumn1, MyColumn2, MyColumn3) =
   (SELECT MyColumn1, MyColumn2, MyColumn3 FROM MyOtherTable WHERE....)

So, the need for joins in UPDATE statements will effectively go away.

The INSERT statement will have some cool improvements like this, which is
actually a table value constructor:

INSERT INTO MyTable (MyColumn1, MyColumn2, MyColumn3)
VALUES (100,'Test 100',10),
(200,'Test 200',20),
(300,'Test 300',30)

etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks guys.
That will do me for the moment.