[Solved] Insert SQL not producing results

Discuss the database features

[Solved] Insert SQL not producing results

Postby nomen » Thu Jan 17, 2019 6:03 pm

I am trying to use two fields from one table and insert them as new rows into a second table with the following code. I am pasting this code into a Tools | SQL window to execute it. The code executes and the window returns a successful completion message. However, when I look at the CaseLabels table, there are no new records added.

Code: Select all   Expand viewCollapse view
INSERT INTO "CaseLabels"
   ("CaseLabel",
    "DiscDescription"
   )
SELECT "MovieTitle" AS CaseLabel,
    "MovieDescription" AS DiscDescription

FROM "Films"
WHERE "MovieTitle" LIKE 'M*A*S*H*' AND
   "Length" IS NULL
ORDER BY "MovieTitle" ASC


Here are what I have tried:
--Removed the aliases on the SELECT statement
--Removed the ORDER BY clause
--Added table names to all fields
--Made sure that the data types of the fields (source and destination) are the same
--Executed the SELECT portion separately (which does show results)

Based on looking at several SQL sites, I am believe the overall syntax is correct. I use similar code to essentially duplicate records in one table and that works fine. This is the first time I have tried to use two tables.

If it matters, this is using an Embedded database file.

Are there any thoughts on why the code seems to execute, but not produce results?
Last edited by nomen on Thu Jan 17, 2019 8:02 pm, edited 1 time in total.
LibreOffice 5.3.6.1 on PCLinuxOS
nomen
 
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm

Re: Insert SQL not producing results

Postby UnklDonald418 » Thu Jan 17, 2019 7:26 pm

Are there any thoughts on why the code seems to execute, but not produce results?

When you use Tools>SQL you are bypassing the Base front end is working directly with underlying HSQL database engine. To inform the Base front end of the changes select View>Refresh Tables.
Last edited by UnklDonald418 on Thu Jan 17, 2019 9:13 pm, edited 1 time in total.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1085
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Insert SQL not producing results

Postby nomen » Thu Jan 17, 2019 7:34 pm

I tried executing the code again, then View>Refresh Tables, then opened the table. There are no new records in the table.
LibreOffice 5.3.6.1 on PCLinuxOS
nomen
 
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm

Re: Insert SQL not producing results

Postby Sliderule » Thu Jan 17, 2019 7:43 pm

Try the following INSERT statement:

Code: Select all   Expand viewCollapse view
INSERT INTO "CaseLabels"
   ("CaseLabel",
    "DiscDescription"
   )
(SELECT "MovieTitle" AS "CaseLabel",
    "MovieDescription" AS "DiscDescription"

FROM "Films"
WHERE "MovieTitle" = 'M*A*S*H*' AND
   "Length" IS NULL
ORDER BY "MovieTitle" ASC)

Explanation:
    Changes I made:
  1. I surrounded the alias you assigned in the the SELECT statement with double quotes to match the case ( CASE: UPPER, Mixed, lower ) of your INSERT statement
  2. The SELECT statement is surrounded by parentheses
  3. The WHERE clause does NOT need a LIKE but rather just an =

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1166
Joined: Thu Nov 29, 2007 9:46 am

Re: Insert SQL not producing results

Postby nomen » Thu Jan 17, 2019 7:53 pm

I made the changes you suggested, but still no new records.
LibreOffice 5.3.6.1 on PCLinuxOS
nomen
 
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm

Re: Insert SQL not producing results

Postby nomen » Thu Jan 17, 2019 8:02 pm

I just figured it out. It was all me. I was using a * in the LIKE, but should have been using a % before the closing quote. When I make that change, all the records are added. Thanks to all for the help.
LibreOffice 5.3.6.1 on PCLinuxOS
nomen
 
Posts: 11
Joined: Tue Nov 06, 2012 6:52 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests