Create type in db2 8.2 - Page 2

prodigy201 · #16 (**permalink**) 01-30-12, 02:31

Quote:

Originally Posted by tonkuma

I don't know Java.
What value was in str, after the assignment?

And, to what query do you want to pass the values?

By the way,
from where came val1, val2, ..., val_n?
We might be better to go back more, to apply the following consideration.

Yeah, we will go back to the query you have provided. Your query is useful indeed!

Code:

INSERT INTO
 person_names(name)
SELECT new_name
 FROM  (VALUES
            'name_1'
          , 'name_2'
          ...
          , 'name_n'
       ) t(new_name)
 WHERE new_name NOT IN (
       SELECT name
        FROM  person_names
       )
;

Now I have 2 problems.
1) If I've sequence for my table, how can I modify my query to insert records using that query using sequence to increment in primary key value?
For e.g. if my table employee has two fields emp_id(primary key) and emp_name. And have sequence set for incrementing (auto incrementation using sequence) primary key emp_Id value, then how can I rewrite insert query similar to your given query?

2) If I've more than one field for e.g. I have my table as emp_table and have two fields emp_fname and emp_lname, then how can I rewrite your above given insert query for inserting 2 field values by avoiding duplicate insertion or already existing data?

And yeah, thanks a lot for such a great help. As I am new to database handling so it's always good to get guidance from all of you. :-)

tonkuma · #17 (**permalink**) 01-30-12, 07:26

It's troublesome for me to polish words and sentenses in English.

So, I'll show an example.

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE emp_table
( emp_id    INTEGER     NOT NULL
            GENERATED ALWAYS AS IDENTITY
, emp_fname VARCHAR(20) NOT NULL
, emp_lname VARCHAR(20) NOT NULL
, PRIMARY KEY (emp_id)
, UNIQUE (emp_fname , emp_lname)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO
 emp_table(emp_fname , emp_lname)
SELECT new_fname , new_lname
 FROM  (VALUES
           ('Alice'  , 'Wonder'  )
         , ('Betty'  , 'Lawrence')
         , ('Cyndi'  , 'Lauper'  )
         , ('Betty'  , 'Smith'   )
         , ('Elen'   , 'Smith'   )
       ) t(new_fname , new_lname)
 WHERE (new_fname , new_lname) NOT IN (
       SELECT emp_fname , emp_lname
        FROM  emp_table
       )
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM emp_table
;
------------------------------------------------------------------------------

EMP_ID      EMP_FNAME            EMP_LNAME           
----------- -------------------- --------------------
          1 Alice                Wonder              
          2 Betty                Lawrence            
          3 Cyndi                Lauper              
          4 Betty                Smith               
          5 Elen                 Smith               

  5 record(s) selected.


------------------------------ Commands Entered ------------------------------
INSERT INTO
 emp_table(emp_fname , emp_lname)
SELECT new_fname , new_lname
 FROM  (VALUES
           ('Cyndi'  , 'Lauper'  )
         , ('Flower' , 'Norm'    )
         , ('Elen'   , 'Smith'   )
         , ('Helen'  , 'O''Neill')
       ) t(new_fname , new_lname)
 WHERE (new_fname , new_lname) NOT IN (
       SELECT emp_fname , emp_lname
        FROM  emp_table
       )
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM emp_table
;
------------------------------------------------------------------------------

EMP_ID      EMP_FNAME            EMP_LNAME           
----------- -------------------- --------------------
          1 Alice                Wonder              
          2 Betty                Lawrence            
          3 Cyndi                Lauper              
          4 Betty                Smith               
          5 Elen                 Smith               
          6 Flower               Norm                
          7 Helen                O'Neill             

  7 record(s) selected.

prodigy201 · #18 (**permalink**) 02-02-12, 01:35

Thanks a lot! It working out! :-) Really appreciate! :-)

Quote:

Originally Posted by tonkuma

It's troublesome for me to polish words and sentenses in English.

So, I'll show an example.

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE emp_table
( emp_id    INTEGER     NOT NULL
            GENERATED ALWAYS AS IDENTITY
, emp_fname VARCHAR(20) NOT NULL
, emp_lname VARCHAR(20) NOT NULL
, PRIMARY KEY (emp_id)
, UNIQUE (emp_fname , emp_lname)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO
 emp_table(emp_fname , emp_lname)
SELECT new_fname , new_lname
 FROM  (VALUES
           ('Alice'  , 'Wonder'  )
         , ('Betty'  , 'Lawrence')
         , ('Cyndi'  , 'Lauper'  )
         , ('Betty'  , 'Smith'   )
         , ('Elen'   , 'Smith'   )
       ) t(new_fname , new_lname)
 WHERE (new_fname , new_lname) NOT IN (
       SELECT emp_fname , emp_lname
        FROM  emp_table
       )
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM emp_table
;
------------------------------------------------------------------------------

EMP_ID      EMP_FNAME            EMP_LNAME           
----------- -------------------- --------------------
          1 Alice                Wonder              
          2 Betty                Lawrence            
          3 Cyndi                Lauper              
          4 Betty                Smith               
          5 Elen                 Smith               

  5 record(s) selected.


------------------------------ Commands Entered ------------------------------
INSERT INTO
 emp_table(emp_fname , emp_lname)
SELECT new_fname , new_lname
 FROM  (VALUES
           ('Cyndi'  , 'Lauper'  )
         , ('Flower' , 'Norm'    )
         , ('Elen'   , 'Smith'   )
         , ('Helen'  , 'O''Neill')
       ) t(new_fname , new_lname)
 WHERE (new_fname , new_lname) NOT IN (
       SELECT emp_fname , emp_lname
        FROM  emp_table
       )
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM emp_table
;
------------------------------------------------------------------------------

EMP_ID      EMP_FNAME            EMP_LNAME           
----------- -------------------- --------------------
          1 Alice                Wonder              
          2 Betty                Lawrence            
          3 Cyndi                Lauper              
          4 Betty                Smith               
          5 Elen                 Smith               
          6 Flower               Norm                
          7 Helen                O'Neill             

  7 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech