I understand now a little better, how SELECT statement works.
Thank you all for the explanations.
Hick Gunter 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
> 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 (
> idINT 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
