Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Question Unanswered: how to select diff columns from 2 tables with some conditions

    Hi

    I have 2 tables , table WORK has numbers instead of names in column 'name' , and those
    have to be replaced with names from bus_name column in BUSINESS table only if work_name column of WORK table matches
    bus_code column values of BUSINESS table.

    For which i created a temp table WT and trying to insert values into WT and
    then truncate table WORK and again insert back records into WORK from WT table

    step 1: ITs not working pls help me with sybase sql
    insert WT
    select A.id,B.bus_name from work A,business B
    where A.work_name = B.bus_code
    and B.bus_code not in('005','006')

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Shouldn't you be using an update statement?

    Can you give a few examples of data that should and shouldn't be changed then we can supply some SQL.

  3. #3
    Join Date
    Oct 2009
    Posts
    8
    Hi Mike ,

    WORK table looks like this
    id work_name
    1 001
    2 002
    3 005
    BUSINESS table looks like this
    bus_name bus_code
    WK tech 001
    CMA tech 002
    LI LTD 005

    I need to match BUSINESS.bus_code with WORK.work_name and change WORK.work_name with BUSINESS.bus_name

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    That wasn't really what I was after - I wanted to see some examples of when to update and when not to update but I think I can guess now.
    Code:
    update WORK
    set    work_name = b.bus_name
    from   BUSINESS b
    where  WORK.work_name = b.bus_code
           and b.bus_code not in ('005','006')
    I can't test the above and I'm only guessing what you're doing. You'd obviously want to run this within a transaction and roll it back if there's any issues.

  5. #5
    Join Date
    Oct 2009
    Posts
    8
    Hi Mike

    Is it sybase sql ..
    bec., i dont see any update happening running the above query ..

    what might be the other problems ..it is not even showing any errors when i execute the update statement ..i am even using commit statement .

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Are there any rows to change? I've changed it into a select statement so you can mess around with this until it selects the rows you want updating and then we'll turn it into an update statement.
    Code:
    select WORK.id, WORK.work_name , b.bus_name
    from WORK, BUSINESS b
    where  WORK.work_name = b.bus_code
           and b.bus_code not in ('005','006')
    In the example data you gave I assume it should of changed 001 -> WK tech and 002 => CMA tech.

    It's also not a good idea doing the commit until you're sure it worked ok.

  7. #7
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    rxg

    It is not Sybase, you are experiencing difficulty with simple ANSI SQL (Sybase, DB2, MS or Oracle). You might be looking for this:
    Code:
    UPDATE WORK SET
        work_name = b.bus_name
        FROM WORK     w,
             BUSINESS b
        WHERE w.work_name = b.bus_code
    But a more important question is, why are you duplicating the bus_name column into the WORK table ?
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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