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

Discuss the database features

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

Postby Jerold » Sun Sep 16, 2018 11:50 pm

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

Re: "INSERT INTO SELECT" can find table column

Postby robleyd » Mon Sep 17, 2018 2:58 am

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#"
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3045
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: "INSERT INTO SELECT" can find table column

Postby chrisb » Mon Sep 17, 2018 12:39 pm

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#"
 
Last edited by chrisb on Mon Sep 17, 2018 2:57 pm, edited 1 time in total.
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

Re: "INSERT INTO SELECT" can find table column

Postby Jerold » Mon Sep 17, 2018 2:57 pm

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

Re: "INSERT INTO SELECT" can find table column

Postby chrisb » Mon Sep 17, 2018 3:34 pm

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.
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 197
Joined: Mon Jun 07, 2010 4:16 pm

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

Postby Jerold » Mon Sep 17, 2018 5:28 pm

Thank you very much.
Your code worked and the explanation was very helpfull.
OpenOffice 4.1.5 on Windows 10 Home
Jerold
 
Posts: 9
Joined: Sun Sep 16, 2018 11:18 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests