I need some help with this SQL statement

Discuss the database features

I need some help with this SQL statement

Postby Craig209Smith » Sun Oct 18, 2020 5:35 am

When I execute the following SQL Statement I get the following status message: Column not found:fWord. I know the first line is correct as I can execute it by itself.

delete from "tbPositions"
SELECT * FROM "tbWordlist" INNER JOIN "tbPositions" ON "fKey" = "fWordlist1"
Insert into "tbPositions"
(
"Len","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15"
)
values(
Length("fWord"),
substr("fWord",1,1),
substr("fWord",2,1),
substr("fWord",3,1),
substr("fWord",4,1),
substr("fWord",5,1),
substr("fWord",6,1),
substr("fWord",7,1),
substr("fWord",8,1),
substr("fWord",9,1),
substr("fWord",10,1),
substr("fWord",11,1),
substr("fWord",12,1),
substr("fWord",13,1),
substr("fWord",14,1),
substr("fWord",15,1)
) ;

I retyped “fWord” the 16 places it appears in the statement and got the same message. I replaced all the “fWord” with fWord (without double quotes) and got the same message. I replaced all the “fWord” with 'fWord' (single quote) and it sort of worked but it only inserted one record, but I wanted to insert 81 records (all the records in tbWordlist.

Len = 5, P1 = f, P2 = W, P3 = o, P4 = r, P5 = d

so I rewrote the SQL as the following but got this message: Column count does not match in statement [Insert into "tbPositions"
values( …......

delete from "tbPositions"
SELECT * FROM "tbWordlist" INNER JOIN "tbPositions" ON "fKey" = "fWordlist1"
Insert into "tbPositions"
values(

Length("fWord"),
substr("fWord",1,1),
substr("fWord",2,1),
substr("fWord",3,1),
substr("fWord",4,1),
substr("fWord",5,1),
substr("fWord",6,1),
substr("fWord",7,1),
substr("fWord",8,1),
substr("fWord",9,1),
substr("fWord",10,1),
substr("fWord",11,1),
substr("fWord",12,1),
substr("fWord",13,1),
substr("fWord",14,1),
substr("fWord",15,1)

) ;

Here is how tbPositions (Used to find words based on criteria) is defined:

Len [TINYINT] The length of the word
P1 [CHAR(1)] Character in 1st position of the word
P2 [CHAR(1)] Character in 2nd position of the word
P3 [CHAR(1)] etc
P4 [CHAR(1)] etc
P5 [CHAR(1)] etc
P6 [CHAR(1)] etc
P7 [CHAR(1)] etc
P8 [CHAR(1)] etc
P9 [CHAR(1)] etc
P10 [CHAR(1)] etc
P11 [CHAR(1)] etc
P12 [CHAR(1)] etc
P13 [CHAR(1)] etc
P14 [CHAR(1)] etc
P15 [CHAR(1)] etc
fWordlist1 [BIGINT] Foreign key to tbWordlist
fKey [BIGINT AutoValue] Primary key

How do I deal the last two columns (fWordlist1 and fKey) which I do not want to change?

Thanks very much in advance
Windows 10 & OpenOffice 4.1.7
OpenOffice 4.1.2 on Windows 10
Craig209Smith
 
Posts: 11
Joined: Thu Nov 15, 2018 1:17 pm

Re: I need some help with this SQL statement

Postby keme » Sun Oct 18, 2020 8:53 am

I believe that you need the SELECT clause as a subclause to INSERT, not the other way around.

Also, when you empty tbPositions, any INNER JOIN on tbPositions will also be empty.
  • If you want to input data into only existing records in tbPositions, skip the initial DELETE and use UPDATE instead of INSERT.
  • If you want to transfer everything from tbWords, omit the JOIN.
    SELECT INTO may be what you need. It may require a bit more work to build the data for insertion, but in the end easier to read/debug.
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests