DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Updating rows in table problem

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Updating rows in table problem

    I am having some problems with an update query

    I have the table (equip_comp) below

    Location Equipment Problem Company ID

    1001 Drill Power ABC Corp 1
    1001 Drill Breakage ABC Corp 2
    1001 Saw Blunt XYZ Corp 3
    1001 Saw Misc NONE 4
    1002 Drill Power AAA Corp 5
    1002 Drill Breakage NONE 6
    1002 Saw Blunt BBB Corp 7
    1002 Saw Misc NONE 8

    I wish to update my table so that where company=NONE then the existing Company for that LOCATION+EQUIPMENT replaces the NONE. For example at I want the company at row identified 1001+SAW to be XYZ.

    I need one query that will look at the existing location + location where it finds a company=NONE, and use the existing non “NONE” company to update the “NONE”. The location+equipment determines the company.

    I assume I am looking at something like

    Update equip_comp set (select distinct compant …..???)

    Thanks

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    update equip_comp ec
    set company =
      (select min(company) from equip_company
       where location = ec.location
         and equipment = ec.equipment
         and company != 'NONE')
    where company = 'NONE'
      and exists (select null from equip_company
                   where location = ec.location
                     and equipment = ec.equipment
                     and company != 'NONE');
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width