[Solved] "INSERT INTO SELECT" can't find table column

[Jerold]

When I run the following on Windows 10 Home using OpenOffice 4.1.5:
INSERT INTO "Res" ("Count")
SELECT "Count" FROM "UpdCount"
WHERE "Res"."Resident" = "UpdCount"."Resident" AND "Res"."DEVICE#" = "UpdCount"."DEVICE#"

I receive:
1: Column not found: Res.Resident in statement [INSERT INTO "Res" ("Count")
SELECT "Count" FROM "UpdCount"
WHERE "Res"."Resident" = "UpdCount"."Resident" AND "Res"."DEVICE#" = "UpdCount"."DEVICE#"
]

even though The "Res" table is keyed on "Resident"

NOTE Solved by Chrisb's UPDATE suggestion:
UPDATE "Res" SET "Count"
(select "Count" from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")
where 1 in
(select 1 from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")

[robleyd]

You need to include the table Res in your SELECT...FROM

Code: Select all   Expand viewCollapse view

INSERT INTO "Res" ("Count")
SELECT "Count" FROM "UpdCount", "Res"
WHERE "Res"."Resident" = "UpdCount"."Resident" AND "Res"."DEVICE#" = "UpdCount"."DEVICE#"

[chrisb]

Jerold,
your code suggests that your aim is to 'update' rather than 'insert' table data.
backup the table "Res" before executing the code below from menu:Tools>SQL.
hit the 'Tables' icon then menu:View>Refresh Tables.

Code: Select all   Expand viewCollapse view

update "Res" set "Count"=
(select "Count" from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")
where 1 in
(select 1 from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")


Edit: 17 Sep 2018 13:56 assuming that you do wish to insert then user robleyd is correct in that the table "Res" must be included in the select clause. note: because the field "Count" is contained in both tables you should be explicit in your selection as below:- Code: Select all   Expand viewCollapse view insert into "Res" ("Count") select "UpdCount"."Count" from "UpdCount" join "Res" on "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#"

[Jerold]

Thank you. My goal isto update "Count" in "Res" from "Count" in "UpdCount"
Before I try this, can you explain the purpose of the "Where 1 in (SELECT 1 ......) is?

[chrisb]

Jerold, i was adding an edit to my previous post as you were replying.

the '1' is a placeholder it could be any value.
the initial select clause

Code: Select all   Expand viewCollapse view

(select "Count" from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")

will always return a single value ("Count" if it exists or NULL if it does not).

to prevent the insertion of null into our table we add the where clause.

Code: Select all   Expand viewCollapse view

(select 1 from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")

when the where clause proves true then '1' is returned & the value of "Count" is inserted.
when the where clause proves false then null is returned & the insert is aborted.

[Jerold]

Thank you very much.
Your code worked and the explanation was very helpfull.