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#")
[Solved] "INSERT INTO SELECT" can't find table column
[Solved] "INSERT INTO SELECT" can't find table column
Last edited by Jerold on Mon Sep 17, 2018 5:26 pm, edited 3 times in total.
OpenOffice 4.1.5 on Windows 10 Home
Re: "INSERT INTO SELECT" can find table column
You need to include the table Res in your SELECT...FROM
Code: Select all
INSERT INTO "Res" ("Count")
SELECT "Count" FROM "UpdCount", "Res"
WHERE "Res"."Resident" = "UpdCount"."Resident" AND "Res"."DEVICE#" = "UpdCount"."DEVICE#"
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: "INSERT INTO SELECT" can find table column
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.
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
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
|
Last edited by chrisb on Mon Sep 17, 2018 2:57 pm, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: "INSERT INTO SELECT" can find table column
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?
Before I try this, can you explain the purpose of the "Where 1 in (SELECT 1 ......) is?
OpenOffice 4.1.5 on Windows 10 Home
Re: "INSERT INTO SELECT" can find table column
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 clausewill 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.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.
the '1' is a placeholder it could be any value.
the initial select clause
Code: Select all
(select "Count" from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")
to prevent the insertion of null into our table we add the where clause.
Code: Select all
(select 1 from "UpdCount" where "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#")
when the where clause proves false then null is returned & the insert is aborted.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: [SOLVED]"INSERT INTO SELECT" can't find table column
Thank you very much.
Your code worked and the explanation was very helpfull.
Your code worked and the explanation was very helpfull.
OpenOffice 4.1.5 on Windows 10 Home