Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: UPDATE based on other tables

    Is it possible to UPDATE a table based on other tables, I.E join to other tables in the UPDATE.

    EXAMPLE (from my understanding so far):
    UPDATE t1 SET col = (SELECT col2 FROM t2 WHERE ...)

    This will update t1 but if the select doesn't return any results it will assign NULL to col.

    What i would like to do is this (don't know the syntax though):
    UPDATE t1 INNER JOIN t2 ON t1.col = t2.col2 SET col = 'test'

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What you say seems possible ...
    It will be useful if you post the table structure and an example of what result you want ..

    BTW, you should be able to add an appropriate predicate to avoid setting column to NULL
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi ApoPen,

    It is posible using alias of the table:
    UPDATE T1 A SET A.COL1 = (SELECT B.COL1 FROM T2 B WHERE A.COL2 = B.COL2) WHERE A.COL2 IN (SELECT C.COL2 FROM T2 C)

    You can also try this:
    UPDATE T1 A SET A.COL1 = (COALESCE(SELECT B.COL1 FROM T2 B WHERE A.COL2 = B.COL2), A.COL1)

    Please send reply if this solves your problem.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    Here is another example. I can't post my table structure unfortunatly.

    CREATE TABLE t1(
    t1_col1 VARCHAR(10) NOT NULL,
    t1_col2 VARCHAR(40) NOT NULL,
    t1_col3 SMALLINT
    );

    CREATE TABLE t2(
    t2_col1 VARCHAR(40) NOT NULL,
    t2_col2 VARCHAR(10) NOT NULL,
    t2_col3 VARCHAR(40) NOT NULL,
    t2_col4 SMALLINT
    );

    CREATE TABLE t3(
    t3_col1 VARCHAR(40) NOT NULL,
    t3_col2 VARCHAR(10) NOT NULL,
    );

    I want to be able to udate multiple columns with a select, but if the select doesn't match; don't update with NULL. The select is doing multiple joins to other tables to verify an update.

    An example update would be:

    UPDATE t1 A SET (A.t1_col1,A.t1_col4) = (SELECT B.t2_col2, C.t2_col3 FROM t2 B INNER JOIN t3 C ON C.t3_col1 = B.t2_col2 WHERE A.t1_col3 = B.t2_col4)

    My statement the update is setting multiple columns and the select is doing more joins. But the general idea is here. Note: these are also large tables.


    thanks

Posting Permissions

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