![]() ![]() Now the above query can be a bit slow for tables that have large amounts of data. NOTE: Update the above query as per your table and column names. In the above query, the id is the primary key of the table, which is used here to pick the same row for performing the update operation. SET mt1.postdate = SUBSTRING_INDEX(mt2.postdatetime," " ,1) Use a SELF JOIN, like this, UPDATE myTable mt1, myTable mt2 If your table name is myTable with columns postdatetime and postdate, then we can run the following query: How to Update a Column with a Substring from another Column in MySQL Here we are breaking the full date time content at space. The column postdate should have the following data: And you want to pick date data from the column postdatetime and update it into the column postdate. If you have the following data in your table with one column postdatetime and another just postdate. Print "yes" # as a test for me to see the loop workedĬonn.In MySQL, if you want to update a column with the value derived from some other column of the same table we can do so by using a SELF JOIN query and if you wish to modify the value derived from another column like maybe get a substring from the text or break the string using some delimiter, then we can use the SUBSTRING_INDEX function in the query.įor example, if you have a column A with some text and you now add a new column D in the same table, and in this new column you want to keep a part of the text stored in column A, then we can do so using the SUBSTRING_INDEX function. 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 |