Results 1 to 7 of 7

Thread: count and merge

  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: merge static value with result set

    Hi- I don't know how basic this question is, as I'm a bit of a Db novice.

    I want to insert a result set from a query into a target table along with some static values that aren't contained in the source table.

    e.g.

    table1
    -------
    fi
    foo

    table2
    -------
    fi
    foo
    fum

    what i want is something akin to

    INSERT INTO table2 (fi, foo, fum) values (SELECT fi, foo FROM table2 where foo='value')

    obviously, my result set only has two columns. since 'fum' has a static value. can i just 'and' it?

    e.g.

    INSERT INTO table2 (fi, foo, fum) values ((SELECT fi, foo FROM table1 where foo='value') AND 'fum_value')

    thanks!
    whoops- (edited)
    Last edited by earlbradley; 12-30-03 at 15:38.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    INSERT INTO table2 (fi, foo, fum) values ((SELECT fi, foo,'FUM_VALUE' FROM table2 where foo='value')

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    Originally posted by gbrabham
    INSERT INTO table2 (fi, foo, fum) values ((SELECT fi, foo,'FUM_VALUE' FROM table2 where foo='value')
    thanks for the speedy response(!)

    i inadvertantly typed out the SELECT from table2 when I meant for it to be from table1. Can I do a select that includes a static value for a column type that doesn't exist for table1?

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    ALTER TABLE table2 MODIFY (
    fum VARCHAR2 (20) DEFAULT 'Your-static-value');
    So You do not have to insert the value every time for column fum.

    If you execute following statement, fum column will be having 'your-static-value' value.


    INSERT INTO table2 values (SELECT fi, foo FROM table1 WHERE condition)

    I guess it should work for you.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    YES ...
    select column1, column2, '1' from table_name ....

    HTH
    Gregg

  6. #6
    Join Date
    Dec 2003
    Location
    Kekaha, Kauai, Hawaii
    Posts
    22
    If you miss any columns it's easy to update them later:

    Update table2 set <column> = 'value'
    where column1='value'

    or other such syntax.

  7. #7
    Join Date
    Dec 2003
    Posts
    14
    I should have been a little clearer- The static value is only static during an iteration of inserts, of which there are many. In order to updates would be more work. Also, in my real scenario, there are many extra columns, not just one. Gregg seems to have provided the correct answer for me above.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •