Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: How do you populate a new column from other tables?

    I need to write a SQL bridge to add a new column to an existing table.

    What I add the new column, I set a default of 0. I then
    want to do an UPDATE to fill it with the appropriate value.
    How do I iterate over all of the rows to set a correct value?

    Here is a MS SQL statement which used to do the trick, but
    DB2 does not like it.

    UPDATE N SET N.NODECLASS=M.MACHCLASS FROM
    MSTRESRC.NODES N, MSTRDATA.MACHINES M
    WHERE N.MACHID=M.MACHID;

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    UPDATE N SET N.NODECLASS = (SELECT M.MACHCLASS FROM
    MSTRESRC.NODES N, MSTRDATA.MACHINES M
    WHERE N.MACHID=M.MACHID);

    Note: this assume that N is the name of the table. If not, use the name of the table for the N in "UPDATE N"
    Last edited by Marcus_A; 09-05-03 at 17:53.

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    UPDATE MSTRESRC.fooNODES SET NODECLASS =
    (SELECT M.MACHCLASS FROM MSTRESRC.fooNODES N, MSTRDATA.MACHINES M WHERE N.MACHID=M.MACHID);

    gives:

    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
    INTO statement is more than one row. SQLSTATE=21000

    I want to update NODECLASS in every row of fooNODES.

    How do I do that with one UPDATE?

    I tried writing a PROCEDURE but gave up.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't really understand how the data is organized on the two tables. Maybe you just need:

    UPDATE MSTRESRC.fooNODES A
    SET NODECLASS =
    (SELECT B.MACHCLASS FROM MSTRDATA.MACHINES B WHERE A.MACHID=B.MACHID);

    The subselect must return just one value (and one row), so I am assuming the MACHID is unique on table MSTRDATA.MACHINES.

  5. #5
    Join Date
    Aug 2003
    Posts
    58
    After much syntax struggle I finally got the following to work:

    BEGIN ATOMIC
    FOR V AS
    SELECT NODEID, MACHID FROM MSTRESRC.fooNODES DO
    UPDATE MSTRESRC.fooNODES
    SET NODECLASS = (SELECT M.MACHCLASS FROM
    MSTRDATA.MACHINES M WHERE M.MACHID=V.MACHID)
    WHERE NODEID=V.NODEID ;
    END FOR;
    END@

Posting Permissions

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