Page 1 of 1

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

PostPosted: Sun Sep 16, 2018 11:50 pm
by 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#")

Re: "INSERT INTO SELECT" can find table column

PostPosted: Mon Sep 17, 2018 2:58 am
by 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#"

Re: "INSERT INTO SELECT" can find table column

PostPosted: Mon Sep 17, 2018 12:39 pm
by 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#"
 

Re: "INSERT INTO SELECT" can find table column

PostPosted: Mon Sep 17, 2018 2:57 pm
by 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?

Re: "INSERT INTO SELECT" can find table column

PostPosted: Mon Sep 17, 2018 3:34 pm
by 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.

Re: [SOLVED]"INSERT INTO SELECT" can't find table column

PostPosted: Mon Sep 17, 2018 5:28 pm
by Jerold
Thank you very much.
Your code worked and the explanation was very helpfull.