Page 1 of 1
[Solved] How to update an embedded HSQL table from another?
Posted: Wed Jul 13, 2022 1:47 pm
by 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"
Re: How to update an embedded HSQL table from another?
Posted: Wed Jul 13, 2022 3:22 pm
by 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 )
Re: How to update an embedded HSQL table from another?
Posted: Wed Jul 13, 2022 5:25 pm
by 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.
Re: [Solved] How to update an embedded HSQL table from another?
Posted: Fri Jul 15, 2022 1:48 pm
by 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)
Re: [Solved] How to update an embedded HSQL table from another?
Posted: Fri Jul 15, 2022 2:34 pm
by Villeroy