There is no DATE datatype in SQLite. Your declaration assigns NUMERIC affinity for the date column.
It seems that you are storing TEXT values, which is allowed, but in conflict with your declaration. Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE alone would have done. As SQLite is asserting a UNIQUE constraint violation, you have probably already inserted at least one saturday into your SchoolYearTeachingDays table. You can check this with: SELECT rowid, aDate FROM SchoolYearTeachingDays WHERE aDate IN (SELECT aDate FROM TeachingSaturdaysInSchoolYear); Or you may have duplicate dates (you did not declare aDate to be UNIQUE in that table) in your TeachingSaturdaysInSchoolYear table, which you can check with: SELECT aDate,count() from TeachingSaturdaysInSchoolYear group by 1 order by 1; To answer your question: INSERT INTO ... SELECT will attempt to insert each result row of the SELECT exactly once. If you want to insert duplicated rows of the SELECT only once, you need to SELECT DISTINCT. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Csányi Pál Gesendet: Dienstag, 26. Juni 2018 15:15 An: SQlite User <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Understanding SELECT statement Hi, I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' table: CREATE TABLE TeachingSaturdaysInSchoolYear ( id INT PRIMARY KEY UNIQUE, aDate DATE, TimetableForTheDay TEXT ); This table holds just two dates. These two dates are for two Saturdays. On these two Saturdays we have to teach students. When I do the following query on this table, I get these two records: 2018-04-14 2018-05-05 I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays' table. I am trying with this query: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: UNIQUE constraint failed: SchoolYearTeachingDays.aDate Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) It works. But I am not understanding it at all. I wish to know followings. How many times want to inserts the SELECT query the one of the date from the TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? That is: the how many times wants select statement to insert one record from first table into second table? -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
