LibreOffice, SQL command with inner join

Question

my problem is that I only know the new name of the owner of the house with id_house = 10, so this code should work. If i try to run it in LibreOffice Base, it says unexpected end INNER, requires SET statement [update "t_Owner" inner] Can you help me? Sorry for my bad english D:

update "t_Owner"
inner join "t_house"
    on t_house.id_ownerr = t_Owner.id_ownerr
set "name"='yes'
where "id_house" = '10'

t_Owner columns: id_ownerr, name, surname, address
t_house columns: id_house, id_district, id_ownerr

Scott Dobbins · Answer 1 · 2016-12-08T22:22:26.057

0

 update "t_Owner"
     set "name"='yes' where "id_ownerr" 
          IN ( SELECT "id_ownerr" FROM "t_house" where "id_house" = '10' )

edited Dec 08 '16 at 22:22

answered Dec 08 '16 at 15:21

Scott Dobbins

294
1
8

it doesn't work, it says unexpected end: FROM in statement – robz Dec 08 '16 at 15:59
Do you need the quotes around the Table and fields names? – Scott Dobbins Dec 08 '16 at 16:04
it says that it can't find the table. I have to use "" – robz Dec 08 '16 at 17:05
so? Have you find out something? I really need this code – robz Dec 08 '16 at 21:46
For sql is working, but it changes all names and surnames (name: yes, surname: false) D: – robz Dec 09 '16 at 15:19

score 0 · Answer 2 · answered Mar 08 '17 at 06:31

In LO when using the default embedded data base, HSQLDB 1.8, queries (i.e. things that use a join) are not always updatability. In fact they are almost always not updateable.

To test this for yourself try creating a query with your join and the fields you need and see if it's updateable. Bet it's not.

You can move to using 2.3 (via a split database setup tool), or do what I do which is run in MariaDB and use a connector.

LibreOffice, SQL command with inner join

2 Answers2