Hi, I am trying to go through filtered records and copy the contents, line by line, of field1 to field2.
Why is the field2 being filled with the same value and not it's corresponding field1 value?
Thanks.
import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()
sql = "SELECT * FROM report WHERE type LIKE 'E%'"
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
field1 = row[0]
cursor.execute("UPDATE report SET field2='" + field1 + "'")
conn.commit()
conn.close()
print value of data, row, field1, and the actual query
I can't run this, so there may be some typos.
import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()
sql = "SELECT * FROM report WHERE type LIKE 'E%'"
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
print(f"\nrow: {row}")
field1 = row[0]
sqlstmt = f"UPDATE report SET field2='{field1}'"
print(f"row[0]: {row[0]}\nsqlstmt: {sqlstmt}")
cursor.execute(sqlstmt)
# cursor.execute("UPDATE report SET field2='" + field1 + "'")
conn.commit()
conn.close()
Thanks for the reply.
The variable in being passed to the sqlstmt, and the sqlstmt is being printed correctly with its corresponding value, but when updating the db, all rows are still being updated with a unique value in field2, which corresponds to the value of the last row.
(May-08-2021, 07:36 AM)andrewarles Wrote: [ -> ]but when updating the db, all rows are still being updated with a unique value in field2, which corresponds to the value of the last row.
This is exactly what can be expected.
cursor.execute("UPDATE report SET field2='" + field1 + "'")... contains no "where" clause, so all records are updated. You only see the results of the last update.
Ok, so what would the WHERE clause be in order to update the row that the cursor's on?
We would need to know the structure of the table. What are the columns? And most important: does the table have a unique key? On which column?
There is no unique key, there is a primary key on field1.
There are 15 columns made up of varchar's and integer's.
Allright then. I cannot test it but I think you have to do something like this.
import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()
sql = "SELECT * FROM report WHERE type LIKE 'E%'"
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
print(f"\nrow: {row}")
field1 = row[0]
sqlstmt = f"UPDATE report SET field2='{field1}' where field1 = '{field1}'"
print(f"row[0]: {row[0]}\nsqlstmt: {sqlstmt}")
cursor.execute(sqlstmt)
conn.commit()
cursor.close()
conn.close()
Thanks, but that only updates the first row.
The remaining rows are not updated.