![]() ![]() can correct the record with the NULL value in the newcontacts table and rerun the INSERT statement. INSERT INTO contactlist2 (FirstName,LastName,Email)SELECT FirstName,LastName,Email FROM newcontacts Įrror: contactlist2.LastName may not be NULL Notice that the third record in the newcontacts table has a NULL value for the LastName field. The following table specification for contactlist2 is identical to contactlist1 except that LastName and FirstName field may not be null.ĬREATE TABLE contactlist2 ( FirstName TEXT NOT NULL, LastName TEXT NOT NULL, Email TEXT NOT NULL PRIMARY KEY) can see that 4 new records were added to the table above. Sqlite> INSERT INTO contactlist1 (FirstName,LastName,Email) SELECT FirstName,LastName,Email FROM newcontacts INSERT INTO target_table( field1, field2, field3 ) SELECT field_a, field_b, field_c FROM source_table than copying the data from the newcontacts table and handkeying it into contactlist1 table we can use a single insert statement with a select query. now there are only two records in the table but we also have a table called newcontacts from which we can add to our list. To avoid sending duplicate emails to the same individual we have made the Email address the primary key.ĬREATE TABLE contactlist1 ( FirstName TEXT, LastName TEXT, Email TEXT NOT NULL PRIMARY KEY) In the following simple example we have a table called "contact1" which lists names and email addresses. Print "yes" # as a test for me to see the loop workedĬonn.Inserting Records from another table using a select query Row_table2_class = row1 # catches the name #connect to the database containing the two tablesĬur = conn.execute("SELECT id FROM Table1")Ĭur2 = conn.execute("SELECT id, class FROM Table2") I will leave the optimization to make this faster upto you. Instead, you would want to fetch all the rows for Table1 once, then all the rows for Table2 once and then match them up. Especially because for every id in Table1, you are fetching all the rows for Table2 everytime to match. ![]() This could probably even run.Īlso, this is by no means the most efficient way to do this. I have just typed that code in here directly, so I have not had the chance to test it, but you can look at it to get an idea. You need to first fetch the rows in both tables using SELECT queries before you can test them out the way you want.įind below modified code from what you posted above. You are missing SELECT queries in your code: cur = con.cursor() For every id in rows1, if this id exists in rows2, execute an UPDATE on that particular id in TABLE1.execute a SELECT query on TABLE1 and fetch rows.execute a SELECT query on TABLE2 and fetch rows.Your code logic should go something like this: You need a lot more code than what you have there. I know it's my syntax, but like I said I am new to this so any guidance is greatly appreciated. Print "yes" #as a test for me to see the loop workedĬur.execute("UPDATE Table1 SET classification=? WHERE id=?", (new_class, row_table1))įrom this however I get an operational error. Would you please help? ThanksĪfter the comments of a user I got this code but it still doesn't work: #connect to the database containing the two tablesĬur.execute("SELECT (id, class) FROM Table2") I have been working on this for some time and can't seem to just get it. I read this post Updating a column in one table through a column in another table I understand the logic around it but I can't translate the SQL code into Python. But then if I only connect with Table1 I get the error Table2 is not defined. I know that it's because I put two parameters in the connect method. New_classification = Table2.class # so now instead of Null it should have the class information from table2Ĭur.execute("UPDATE Table1 SET class = ? WHERE id =? ", (new_classification, id))īut, I get an error for line2: TypeError: a float is required. def update_table1():Ĭon = nnect('table1.db', 'table2.db') #I know this is wrong but if how do I connect table2 so that I don't get error that the Table2 global names is not defined? ![]() The problem occurs when I try to update table1 with information from table2. The information to build the two tables comes from two separate files. So the code needs to be able to go through table 2 and whenever it finds a matching id in table 1 to updating the class column (of table 1) from the classification column of table 2. In both tables, the primary key is the id column, the classification and class column would eventually have to be merged. Basically, the two tables have two common columns. (id INT, class TEXT, type TEXT, PRIMARY KEY (id)). I then have a second table which has three columns. In this table, the column for classification is left empty and will be updated by the information from table 2. I have a table with four columns (id INT, other_no INT, position TEXT, classification TEXT, PRIMARY KEY is id). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |