[Solved] Insert SQL not producing results

[nomen]

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

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?

[UnklDonald418]

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.

[nomen]

I tried executing the code again, then View>Refresh Tables, then opened the table. There are no new records in the table.

[Sliderule]

Try the following INSERT statement:

Code: Select all

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:

1. Changes I made:
2. 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
3. The SELECT statement is surrounded by parentheses
4. 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.

[nomen]

I made the changes you suggested, but still no new records.

[nomen]

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.