aaronk Well-Known Member Licensed User Longtime User Jan 6, 2016 #1 Hi, I have a SQLite database which has a table with many rows and columns. Each row has many columns (100 columns) all with different names. Other than deleting the complete row and then recreating that row is there a SQL query I can use to clear the data for 1 row only but keeping the first column data and deleting the remaining column data ? For Example: Based on my example below, how would I clear row 1 but keep 123 (since that will unique) for this row?
Hi, I have a SQLite database which has a table with many rows and columns. Each row has many columns (100 columns) all with different names. Other than deleting the complete row and then recreating that row is there a SQL query I can use to clear the data for 1 row only but keeping the first column data and deleting the remaining column data ? For Example: Based on my example below, how would I clear row 1 but keep 123 (since that will unique) for this row?
eurojam Well-Known Member Licensed User Longtime User Jan 6, 2016 #2 you can get all columnnames like this: Code: Copy Dim cur As ResultSet cur = aSQL.ExecQuery("PRAGMA table_info(mytable);") Do While cur.NextRow Log(cur.GetString("name")) Loop then you can build your update statement in the loop or put the field names in the a map and use dbutils with Code: Copy ' updates multiple fields in a record ' in the Fields map the keys are the column names Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map) Upvote 0
you can get all columnnames like this: Code: Copy Dim cur As ResultSet cur = aSQL.ExecQuery("PRAGMA table_info(mytable);") Do While cur.NextRow Log(cur.GetString("name")) Loop then you can build your update statement in the loop or put the field names in the a map and use dbutils with Code: Copy ' updates multiple fields in a record ' in the Fields map the keys are the column names Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map)
aaronk Well-Known Member Licensed User Longtime User Jan 6, 2016 #3 eurojam said: you can get all columnnames like this: Code: Copy Dim cur As ResultSet cur = aSQL.ExecQuery("PRAGMA table_info(mytable);") Do While cur.NextRow Log(cur.GetString("name")) Loop then you can build your update statement in the loop or put the field names in the a map and use dbutils with Code: Copy ' updates multiple fields in a record ' in the Fields map the keys are the column names Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map) Click to expand... Sounds like a good idea on how to do it. thanks for your good idea. Upvote 0
eurojam said: you can get all columnnames like this: Code: Copy Dim cur As ResultSet cur = aSQL.ExecQuery("PRAGMA table_info(mytable);") Do While cur.NextRow Log(cur.GetString("name")) Loop then you can build your update statement in the loop or put the field names in the a map and use dbutils with Code: Copy ' updates multiple fields in a record ' in the Fields map the keys are the column names Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map) Click to expand... Sounds like a good idea on how to do it. thanks for your good idea.