-1

I need to make all my products to have a caracteristic(id 5) so im trying to make a procedure that will insert into a table 'product-caracteristics' a line with its atributes, linking all products IDs with the caracteristic id 5. Thats what I have so far :

BEGIN
    FOR SELECT ID-PROD FROM PRODUCT INTO :VAR1 DO
    BEGIN
        INSERT 
            INTO PRODUCT-CARACTERISTICS(
                ID-PRODUCT-CARACTERISTICS, //char9
                ID-CARACTERISTICS,         //char9
                ID-PROD,                   //char9
                DATE-CARACTERISTICS        //date
                )
            VALUES (
                '', //its a pk, so it should be automatic, right?... 
                '000000005', 
                :VAR1, 
                '04.03.2015'
                );
    END
END

Another thing, if possible i would like to know how to make this only for the products that doesnt have caracteristic5.

(if its relevant, the application is IBExpert, using firebird 2.0, SQL dialect 3.0) Thanks in advance.

Ken White
  • 123,280
  • 14
  • 225
  • 444
BrunoWB
  • 129
  • 1
  • 1
  • 15
  • I removed the `'ql-server' tag from your question. SQL Server is a Microsoft DBMS, and has absolutely nothing to do with Firebird. Please read the description of tags before using them, and use only the tags that actually apply to your question. Tags here have specific meanings and purposes. Please don't just add those that have familiar words or seem similar. Thanks. – Ken White Mar 04 '15 at 19:12

1 Answers1

0

A PK doesn't generate IDs automatically. Typically, you can fill the field using a generator :

create generator NUM_PRODUCT_CARACTERISTICS;

and use it :

... VALUES (
                gen_id(NUM_PRODUCT_CARACTERISTICS, 1),
                '000000005', 
                :VAR1, 
                '04.03.2015'
                );

gen_id generate un new unique value that you can use in the insert.

PS : "-" is not allowed in table names and field names... prefer "_"

JPB31
  • 128
  • 5
  • 1
    A minus (`-`) is allowed if (and only if) the objectname is quoted (eg `"id-prod"`). That said, your advise not to use minus is sound. – Mark Rotteveel Mar 05 '15 at 17:04