Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    US
    Posts
    10

    Unanswered: Update statement not working

    I need to join 2 tables to filter rows but update col of only one table.I cannot get this update statement to work.
    Can anyone correct it for me or tell me how to write an update for this scenario? Thanks.

    UPDATE (SELECT *
    FROM Table a,Table b
    WHERE a.c1 = b.c1
    AND a.c2 = b.c2)
    SET (a.c3 = value
    , a.c4 = value)
    WHERE a.c1 = value
    AND a.c2 = value
    AND b.c3 = value;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    start off with getting a select statement to that gets the data you want.
    that is always the easiest way to start off.

    From there it is easy to convert into an update.

    It might end up something like this:

    PHP Code:
    update table_a
    set a
    .c3 = (select b.c3 
                     from table_b b
    table_a a 
                     where b
    .c1 a.c1 
                     
    and a.c2 b.c2 
                     
    and b.c3 value),
         
    a.c4 = (not sure what you want to set this to)
    where a.c1 value
    and a.c2 value
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Location
    US
    Posts
    10
    Thanks, i appreciate it..
    I am not setting any value in table a from table b. Join to Table b is only to filter rows from table a.
    I would'nt be setting a.c3 to any value from b, it would be some input value. The whole deal of involving table b is to check if b.c3 = value.
    How would i do that?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    a procedure with 2 cursors might be easier.
    the second cursor would be the check against the B table.

    otherwise something like this might work.
    PHP Code:
    update table_a
    set a
    .c3 v_c3                 
         a
    .c4 v_c4
    where a
    .c1 value
    and a.c2 value
    and value IN (select b.c3 from table_b where b.c3 value); 
    kinda crazy, but it works I guess.
    double-check the where clause with a select * statement on table_a.
    If you get the rows that you would normally update, then you are in business.
    you could use EXISTS as well.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2003
    Location
    US
    Posts
    10
    Thanks, it worked. You are good.

Posting Permissions

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