SQL: use Update together with Select and Insert for insert data without creating new records ? It&#0-CodePudding

Using Insert and Select, through a cursor.executescript, I copy the data of a column of a table into a new table. Everything works correctly. Use SQLite.

The problem, however, is that "new" row of records are created (the last red colored row in the picture). Instead I would like to insert (copy) the data in the column of the already existing records, so all in the same row. I think i need UPLOAD and not Insert, but I don't know how (using Upload) to copy the data of a column of a table into a new table. For example, I would like to have the data "1" in all records from 1 to 20, and not in row 21 (row 21 must not exist). How can I do? Can you help me with the code? Thanks

cursor.executescript("""
    INSERT INTO ARCHIVIO_Squadre_Campionato (ID_Campionato) #new table in photo
    SELECT ID_Campionato FROM ARCHIVIO_Campionati #old/other table
    WHERE Nome_Campionato='Serie A'; #column name of old/other table
    """)
con.commit()

UPDATE: For a more precise question, I add the two sql tables:

CREATE TABLE "ARCHIVIO_Campionati" (
    "ID_Campionato" INTEGER, #example: 1
    "Nome_Campionato"   INTEGER, #example: Serie A
    PRIMARY KEY("ID_Campionato" AUTOINCREMENT)
);

CREATE TABLE "ARCHIVIO_Squadre_Campionato" (
    "ID_Sq" INTEGER,
    "Nome_Squadra"  TEXT,
    "ID_Campionato" INTEGER,
);

CodePudding user response：

Are you trying to set the championship field for all teams? Editing/updating data is the job of UPDATE.

Try

UPDATE ARCHIVIO_Squadre_Campionato
SET 
    ID_Campionato= (SELECT ID_Sq
                    FROM ARCHIVIO_Campionati
                    WHERE Nome_Campionato = 'Serie A');

It's often a lot easier to get answers if you explain what you want to do, not how you think it should be done

CodePudding user response：

After you inserted these 20 rows in ARCHIVIO_Squadre_Campionato, you have to update the table and not insert new rows:

UPDATE ARCHIVIO_Squadre_Campionato 
SET ID_Campionato = (
  SELECT ID_Campionato 
  FROM ARCHIVIO_Campionati
  WHERE Nome_Campionato='Serie A'
)
WHERE ID_Campionato IS NULL;

The WHERE clause is not really needed because all rows (in the current state of the table) have NULL in the column ID_Campionato but it will be needed in subsequent calls to the same query for a different value (say for 'Serie B' rows).