user1205746 user1205746 - 6 months ago 11
SQL Question

Insert a new record with a field containing total record in the table

I wonder if it exists a function that allows me to get total rec count of a table to for new record insertion into the table?

I have this table:

CREATE TABLE "TEST"
( "NAME" VARCHAR2(20 BYTE),
"ID" NUMBER,
"FLAG" NUMBER
) ;
Insert into TEST (NAME,ID,FLAG) values ('Ahlahslfh',1,1);
Insert into TEST (NAME,ID, FLAG) values ('Buoiuop',2,1);
Insert into TEST (NAME,ID, FLAG) values ('UOIP',12,0);


enter image description here

My intention is to issue a statement that is equivalent to this:

INSERT INTO TEST( NAME, ID, FLAG )
VALUES( 'TST', 3,1 );


The statement I used below generated error:

INSERT INTO TEST ( NAME, ID, FLAG )
VALUES ( 'TST', SELECT COUNT(*)+1 FROM TEST WHERE FLAG=1,1 );


Below is the final result I am expecting:

enter image description here

Is there a way around it? Of course, I can put them in a script, count the records into a variable and insert that variable into the field. I just wonder if there is more elegant solution and do this in 1 statement.

Thanks!

Answer

This is likely to be a very bad way to set an id. In general, I think you should use sequences/identity/auto_increment and not worry about gaps.

But, you can do what you want using parentheses -- these are needed for subqueries:

INSERT INTO 
     TEST(  
          NAME,
          ID, 
          FLAG 
     )VALUES( 
          'TST', 
          (SELECT COUNT(*)+1 FROM TEST WHERE FLAG=1), 
          1 
     );

Or, alternatively:

INSERT INTO 
     TEST(  
          NAME,  
          ID, 
          FLAG 
     )SELECT 
          'TST', 
          COUNT(*) + 1, 
          1
     FROM 
          TEST
     WHERE 
          FLAG = 1;

I must emphasize that this seems dangerous. It is quite possible that you will get duplicate ids. You should really let the database insert a new value and not worry about gaps.