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

Discuss the database features
Post Reply
Jerold
Posts: 9
Joined: Sun Sep 16, 2018 11:18 pm

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

Post 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#")
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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: "INSERT INTO SELECT" can find table column

Post by robleyd »

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.1.2; SlackBuild for 24.2.1 by Eric Hameleers
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: "INSERT INTO SELECT" can find table column

Post 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

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

insert into "Res" ("Count")
select "UpdCount"."Count"
from "UpdCount"
join "Res" on "Res"."Resident"="UpdCount"."Resident" and "Res"."DEVICE#"="UpdCount"."DEVICE#"
 
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
Jerold
Posts: 9
Joined: Sun Sep 16, 2018 11:18 pm

Re: "INSERT INTO SELECT" can find table column

Post 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?
OpenOffice 4.1.5 on Windows 10 Home
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: "INSERT INTO SELECT" can find table column

Post 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

(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

(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.
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
Jerold
Posts: 9
Joined: Sun Sep 16, 2018 11:18 pm

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

Post by Jerold »

Thank you very much.
Your code worked and the explanation was very helpfull.
OpenOffice 4.1.5 on Windows 10 Home
Post Reply