Results 1 to 2 of 2

Thread: Updating columns in a table

  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Updating columns in a table

    Hello all,
    I am trying to update column values in one table with the column values from another table. To make my question easier to understand, here are the tables I have and their column names:

    table name: 'mtest'
    sampyear
    plotnum
    spcode - spcode is something like "RM" for Red Maple
    sptype - sptype for RM would be H for hardwood

    table name: 'density'
    plotnum
    sampyear
    sptype
    spdens

    Now, the values in the plotnum, sampyear, and spcodes are the same for both tables. I would like to fill the sptype column with the values from the sptype column in 'mtest'. I have tried multiple scripts including this most recent one:

    select sptype from mtest
    inner join density on sptype where density.plotnum=mtest.plotnum
    order by plotnum, sampyear;


    I have also tried:
    update density
    set sptype=mtest.sptype;


    Here I get an error saying that I need AND/IN/BETWEEN/OR etc operators. I am sure this has a simple solution that I just haven't been able to find. I just started using SQL with Oracle XE yesterday!

    Thanks in advance,
    Ladygray

  2. #2
    Join Date
    Jul 2008
    Location
    Belgium
    Posts
    17
    Hello


    I hope this will help you.

    UPDATE density
    SET density.sptype =
    (SELECT mtest.sptype
    FROM mtest
    WHERE mtest.plotnum = density.plotnum
    AND mtest.sampyear = density.sampyear)


    Greetings
    ld_be

Posting Permissions

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