CODEWITHSUNDEEP
sqlitesql-insert
randms26
randms26

Reputation: 137

Insert value in sqlite database to the first row in a specified column that is null

I'm trying to write a program that will automatically insert a data into a given table in sqlite. Here's how my table looks like:

enter image description here

For example, I want to input a new value data5 into col3, here's the command I used:

INSERT INTO exam_table (col3) VALUES ('data5')

and here's how the table looks afterwards:

enter image description here

The problem is, if I want to insert a new value into another column, say col1, what command should I use so the new value will be inserted into the first row in col1 that is null (row 2)?

NB: please provide any SQL command except the UPDATE exam_table SET col1='data6' WHERE col3='data4'ones, because it's assumed that we don't know how the table looks like, so we don't know that 'col3' row 2 contained 'data4'

Upvotes: 0

Views: 2590

Answers (1)

pilcrow
pilcrow

Reputation: 58534

A relational database may not be right for your application, or you may need to explicitly impose ordering on your rows.

You ask:

what command should i used so the new value will be inserted into the first row in 'col1' that is null

... but SQL doesn't have a true concept of row ordering outside of order imposed by queries. There is no "first row" with a NULL col1 unless you define the scheme to ORDER BY.

You then ask us not to provide an UPDATE command, "because it's assumed that we dont know how the table looks like, so we dont know that 'col3' row 2 contained 'data4'"

But that is how SQL works: it conceives of things as unordered sets of tuples (with duplicates permitted). You don't "insert a value into a NULL field", you INSERT a whole row or UPDATE individual fields.

You must impose ordering and then work from there — and, yes, it will be an UPDATE command or similar — or drop your SQL backend and use something with a different idea of data ontology.

Upvotes: 2

Related Questions