[Solved] How to update an embedded HSQL table from another?

[Villeroy]

This is how it is documented

Code: Select all

UPDATE table SET column = Expression [, ...] [WHERE Expression];

This is how it should work:

Code: Select all

UPDATE TBLA
SET TXT = TBLB.TXT
FROM TBL2 
WHERE TBLA.ID = TBLB.ID

I get error "Column not found: TBLB.TXT"

[Rafkus_pl]

It works like this for me:

Code: Select all

UPDATE TBLA
SET TXT = (SELECT TXT FROM TBLB WHERE TBLA.ID = TBLB.ID )
WHERE EXISTS (SELECT * FROM TBLB WHERE TBLA.ID = TBLB.ID )

[Villeroy]

Indeed. Thank you very much.
and if there were more columns:

Code: Select all

UPDATE TBLA
SET
  TXT = (SELECT TXT FROM TBLB WHERE TBLA.ID = TBLB.ID ),
  X = (SELECT X FROM TBLB WHERE TBLA.ID = TBLB.ID ),
  Y = (SELECT Y FROM TBLB WHERE TBLA.ID = TBLB.ID ),
  Z = (SELECT Z FROM TBLB WHERE TBLA.ID = TBLB.ID )
WHERE EXISTS (SELECT TXT FROM TBLB WHERE TBLA.ID = TBLB.ID )

as always mind the commas at the ends of some lines.

[Rafkus_pl]

Or you can do it in a slightly roundabout way:
first remove the records from the TBLA table with IDs the same as in the TBLB

Code: Select all

DELETE FROM TBLA 
WHERE ID IN (SELECT ID FROM TBLB)

And then copy the "new" data from the TBLB table to the TBLA

Code: Select all

INSERT INTO TBLA
SELECT * FROM TBLB

Optionally, you can specify one more condition here: add only those records that are not in the TBLA table.

Code: Select all

WHERE NOT ID IN (SELECT ID FROM TBLA)

[Villeroy]

Thanks. Your advice is part of [HSQL, Base, Basic] Macro Driven CSV Import