postgresql - 自动更新

Question

-- Rough Work for MMID Updation Automatically

CREATE TABLE main
(
  sno serial NOT NULL,
  mid integer,
  mmid integer,
  venue text,
  CONSTRAINT pk_sno PRIMARY KEY (sno)
);


INSERT INTO main(mid, mmid, venue) VALUES(1, 1, 'Ramesh Gardens');
INSERT INTO main(mid, mmid, venue) VALUES(1, 2, 'Suresh Gardens');
INSERT INTO main(mid, mmid, venue) VALUES(1, 3, 'Kamesh Gardens');
INSERT INTO main(mid, mmid, venue) VALUES(2, 1, 'Rajesh Gardens');
INSERT INTO main(mid, mmid, venue) VALUES(2, 2, 'Rajesh Gardens');

每当根据 mid 值的变化插入新记录时，我需要将列 mmid 值更新为 1

这里的mid代表Meeting_ID，mmid代表SubMeeting_ID。我试过这样。

SELECT * FROM main;

INSERT INTO main(mid, mmid, venue) VALUES((SELECT MAX(mid) FROM main)+1,(SELECT MAX(mmid) FROM main WHERE mid=(SELECT MAX(mid) FROM main))+1,'Rajesh Gardens');

SELECT * FROM main;

SQL Fiddle demo

Answer 1

在表中保留这样的值很难且容易出错，您确定不想在某些 View 中直接计算它吗:

select
    sno, mid, venue, row_number() over(partition by mid order by sno) as mmid
from main

sql fiddle demo