Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

The database you are using has an effect on what syntax to use. If you are using are the embedded database which comes with Base, then this will work (this also works in newer HSQL versions):

UPDATE "Table1"
   SET "column1" = (SELECT "column1" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id"),
          "column2" = (SELECT "column2" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id")

If you are using a newer version of HSQL (as in some split DB's with v2.3 or such) then this will work:

UPDATE "Table1"
   SET ("column1", "column2") = (SELECT "column1", "column2" FROM "Table2"
      WHERE "Table1"."id"  = "Table2"."id")

The database you are using has an effect on what syntax to use. If you are using are the embedded database which comes with Base, then this will work (this also works in newer HSQL versions):

UPDATE "Table1"
   SET "column1" = (SELECT "column1" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id"),
          "column2" = (SELECT "column2" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id")

If you are using a newer version of HSQL (as in some split DB's with v2.3 or such) then this will work:

UPDATE "Table1"
   SET ("column1", "column2") = (SELECT "column1", "column2" FROM "Table2"
      WHERE "Table1"."id"  = "Table2"."id")

You must run these from the opening .odb menu Tools->SQL... and not in a query.