I understand now a little better, how SELECT statement works. Thank you all for the explanations.
Hick Gunter <h...@scigames.at> ezt írta (időpont: 2018. jún. 26., K, 16:23): > > 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 -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
