I have a table, 'Matches', with details of matches played over several years - the two key fields for the problem I have are 'MatchID' and 'DatePlayed'. 'MatchID' is the Primary Key which is auto-incremented. When a new season/year starts it would be convenient to copy last year's match details to a new year, e.g. all dates for 2017 of type dd/mm/17 would be copied and the date changed to dd/mm/18.
I have successfully:
- Extracted the required data from 'Matches' into a temporary file called 'Temp'
- Updated the year in the 'DatePlayed' field from 17 to 18.
Now I need to insert the records from 'Temp' into 'Matches'. However when I do this with an INSERT FROM statement, the 'MatchID' values in 'Temp', copied from 'Matches', cause a conflict because they are identical.
The question is how can I insert the records from 'Temp' while allowing 'MatchID' to update automatically as it would if a new record were added manually or via a form?
I tried setting all the MatchIDs in 'Temp' to Null, but that did not solve the problem.
[Solved] Inserting records to avoid AutoValue ID conflict
[Solved] Inserting records to avoid AutoValue ID conflict
Last edited by Nocton on Mon Feb 05, 2018 9:09 pm, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Inserting records to avoid AutoValue ID conflict
If "MatchID" in the table "Matches" is auto-incrementing then don't copy "MatchID" from "Temp"
Should append all the records in "Temp" to "Matches"
Code: Select all
INSERT INTO "Matches" SELECT "DatePlayed" FROM "Temp"
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Inserting records to avoid AutoValue ID conflict
Nocton,
no need to select or insert the primary key field, that takes care of itself.
we don't have your field names but it's important to note that the field positions contained in the select & insert clauses must correspond i.e. first field in select clause is saved to first field in insert clause etc.
no need to select or insert the primary key field, that takes care of itself.
we don't have your field names but it's important to note that the field positions contained in the select & insert clauses must correspond i.e. first field in select clause is saved to first field in insert clause etc.
Code: Select all
insert into "Matches"("DatePlayed", "NameOfFieldWhereColumn2DataIsToBeSaved", "NameOfFieldWhereColumn3DataIsToBeSaved")
select "DatePlayed", "Column2", "Column3"
from "Temp"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Inserting records to avoid AutoValue ID conflict
Thank you both. I did not think of that. I was using 'Select *'.
OpenOffice 4.1.12 on Windows 10