I need an SQL table to update a Unit # instead of creating a new entry if it detects a Unit already exists

Question

I am trying to build a database for managing residents that live in a gated community. The goal is to create a function that makes it easy for a property manager to fill out a form then submit information into a database. I need SQL to create a new entry if the unit# doesn't exist but if the unit does exist then I need it to overwrite the entry for that unit# with new data. Here is my code currently. So basically what I am saying is instead of just entering data blindly into the appropriate columns I need it to check to see if that :unit value already exists and if it does overwrite the data already there.

def submit():

    #logging
    logger.info("Resident info added for")
    logger.info(unit.get())
    print(logger.level)

    #Databases
    conn = sqlite3.connect('rdata.db')
    c = conn.cursor()
    c.execute("INSERT INTO rdata VALUES (:name, :unit, :phone1, :phone2, :snote, :snote2, :guest1, :guest2, :guest3, :guest4, :guest5, :guest6, :guest7, :guest8, :guest9, :guest10, :guest11, :guest12, :guest13, :guest14, :guest15)",
        {
            'name': name.get(),
            'unit': unit.get(),
            'phone1': phone1.get(),
            'phone2': phone2.get(),
            'snote': snote.get(),
            'snote2':snote2.get(),
            'guest1': guest1.get(),
            'guest2': guest2.get(),
            'guest3': guest3.get(),
            'guest4': guest4.get(),
            'guest5': guest5.get(),
            'guest6': guest6.get(),
            'guest7': guest7.get(),
            'guest8': guest8.get(),
            'guest9': guest9.get(),
            'guest10': guest10.get(),
            'guest11': guest11.get(),
            'guest12': guest12.get(),
            'guest13': guest13.get(),
            'guest14': guest14.get(),
            'guest15': guest15.get(),
        })

    conn.commit()
    conn.close()

    name.delete(0, END)
    unit.delete(0, END)
    phone1.delete(0, END)
    phone2.delete(0, END)
    snote.delete(0, END)
    snote2.delete(0, END)
    guest1.delete(0, END)
    guest2.delete(0, END)
    guest3.delete(0, END)
    guest4.delete(0, END)
    guest5.delete(0, END)
    guest6.delete(0, END)
    guest7.delete(0, END)
    guest8.delete(0, END)
    guest9.delete(0, END)
    guest10.delete(0, END)
    guest11.delete(0, END)
    guest12.delete(0, END)
    guest13.delete(0, END)
    guest14.delete(0, END)
    guest15.delete(0, END)

Answer 1

You are describing an upsert query. In SQLite, this is implemented with on conflict.

For this to work, you need a unique constraint on column unit - or use that column as primary key.

Then, you can phrase the query as follows. I reduced the number of columns for readability... and because I am suspicious about your design - these 15 guest columns should maybe be rows in another table.

INSERT INTO rdata (name, unit, phone, snote, guest)
VALUES (:name, :unit, :phone, :snote, :guest)
ON CONFLICT(unit)
UPDATE SET
    name  = excluded.name,
    phone = excluded.phone,
    snote = excluded.snote,
    guest = excluded.guest

If a row exists with the same unit, the query updates the existing row with the new values.

Note that I enumerated the target column in the insert: this is a good practice in SQL for several reasons, such as maintenability.