How to assign a corresponding row-id number to each row in a table - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

How to assign a corresponding row-id number to each row in a table

sqlite add tag

FoggyFinder

I have a tables, simplified version of which could look like it:

![tAQ2.jpg](/image?hash=4be306eba455b167b419a0cacf9794b48ad3c52db62f996847dbe516a7c4790d)
after a while I've realised that `SomeTable` table is missing one column - `number`.

I can alter table but default value has to be a constant.

```
ALTER TABLE SomeTable
ADD COLUMN number Integer NOT NULL DEFAULT 0;
```

I know there is [`ROW_NUMBER`](https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/) function but I wasn't able to find good article/answer about how to apply it to data in a table.

To illustrate on a simple sample: 

Current state

| Id | Header  | Timestamp  | number | OtherId |
|----|---------|------------|--------|---------|
| 1  | Header1 | 17.11.2020 | 0      | 1       |
| 2  | Header2 | 18.11.2020 | 0      | 2       |
| 3  | Header3 | 19.11.2020 | 0      | 1       |

Desired output:

| Id | Header  | Timestamp  | number | OtherId |
|----|---------|------------|--------|---------|
| 1  | Header1 | 17.11.2020 | 1      | 1       |
| 2  | Header2 | 18.11.2020 | 1      | 2       |
| 3  | Header3 | 19.11.2020 | 2      | 1       |

Link to a fiddle:

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=e84fd8117a5ac483477eebd4c14f39f4

Top Answer

Jack Douglas

`row_number` does appear to be what you are looking for:

<>https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=57d644b63aea67851c8b9eff5f8777c2&hide=511

you're right - it would be good to have many-to-many there. I just don't think it's feasible for me to implement it now. That's for one of my personal project with low priority. But who knows how it turns in a year or so

As for your solution, this is more or less how I did it here: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a0d67ae9b5eff2f2fce2b44317a99629 Except I also have a filter for the `UPDATE` part to make sure I don't update rows that are not in `DTable`, because that would essentially mean attempting to store nulls in `number`, which would result in an error since the column is non-nullable.

Junction tables are usually used exactly for that purpose, to organise a many-to-many relationship. When you want a 1-to-many, you don't need a junction table. You could just have an `OtherId` column directly in `SomeTable` referencing `OtherTable.Id`.

It's actually the diagram that gave me the impression that it's many-to-many. It shows 1 on one side and * on the other both for `SomeTable` and for `OtherTable`. That basically means that `SomeTable` and `OtherTable` have a many-to-many relationship via the `DTable` junction table.

Sorry, was distracted. I meant to follow up my question with another point. From your description I understand that the `SomeId` value can theoretically be linked to multiple `OtherId` values. If that is so, then you can't expect the `number` to be definite, because `SomeId` can be number 1 for one `OtherId` and, say, number 5 for a different one. Do you see what I mean?

1 Answer