i have a few tables look like below (sqlite)

table a:

a_id (unique)	timestamp
21	123
2	178
33	101
7	221
4	115

table b:

b_id (unique)	a_id	data (no need to sort)
14	4	bb
3	2	dd
22	21	ee
6	7	xx
17	2	tt
11	33	qq

now i want to update these tables' _id according to the timestamp in a as follows:

table a:

a_id	timestamp
1	101
2	115
3	123
4	178
5	221

table b:

b_id	a_id	data
1	1	qq
2	2	bb
3	3	ee
4	4	dd
5	2	tt
6	5	xx

how can i do this easily with sql, without re inserting the data etc?

thanks

i used to create a dummy column with new and sorted ids by timestamp, then update other tables accordingly but it was inefficient and i have forgotten how i did it

update:

to simplify, i want to create a new column sort_id in table a, which holds the value according to the sorted timestamp, so

before:

table a

a_id (unique)	timestamp
21	123
2	178
33	101
7	221
4	115

after:

table a

a_id (unique)	timestamp	sort_id
21	123	3
2	178	4
33	101	1
7	221	5
4	115	2

so how can i fill the sort_id column according to the timestamp?

CodePudding user response：

you want to query your table b data and sort it based on time stamp .

order of rows in a database is not guaranteed at all and it changes anytime after each insert/update, so you need to join two tables and sort based on timestamp column :

select  b.*
from table_b b
left join table_a a 
   on a.a_id = b.a_id
order by a.timestamp

CodePudding user response：

Aftrer you've altered the table to add the new column...

WITH
  sorted AS
(
  SELECT
    a_id,
    ROW_NUMBER() OVER (ORDER BY timestamp) AS new_sort_id
  FROM
    table_a
)
UPDATE
  table_a
SET
  sort_id = sorted.new_sort_id
FROM
  sorted
WHERE
  table_a.a_id = sorted.a_id