SQLite update data from one table to another

I’m trying to insert some data from one SQLite database to another. I have 2 tables called “members” and “attendance.” What’s supposed to happen is, everytime a new member is added into the member table (or removed), the name and ID of the specific member should also be added to the attendance table. Picture below shows what I’m trying to do.

So I want the ID and name to be the same in each table (as shown by the arrows).. The picture also shows the member table only having 5 members, as time goes on there will be more members added and some removed.

Did some research and thought this would work but it returns a sqlite3.OperationalError: near "FROM": syntax error error.

CURSOR.execute('''
    UPDATE 
        attendance
    SET    
        id = members.id,
        name = members.name
    FROM
        members
;''')

NOTE: There is a CURSOR.execute because I’m doing this is python using the sqlite3 library as required by my teacher.

If anyone could help me with this that would be great, thanks. If something didn’t make sense, please let me know. Thanks again

Answer

Not update but you need to insert rows in attendance table like below.

CURSOR.execute('''
    insert into attendance(id,name)
    select id ,name
    FROM members;''')

But I don’t think you want to insert all the members every time. Either you can specify a member with where clause like where id= 1 or you need to exclude the members already available in attendance table. You can use not exists as below:

CURSOR.execute('''
        insert into attendance(id,name)
        select id ,name
        FROM members m 
        where not exists
                        (
                           select 1 from attendance where attendance.id=m.id
                        )    ;''')

Above query will only insert members not already exists in attendance table.