Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

assign based on changing row

Company                                                  Assigned to      
Airgas USA, LLC                                                            POLLACKA
An Ping County Fireworks General Factory            POLLACKA
Brothers Pyrotechnics, Inc.                                            POLLACKA
Changsha Huibaijin Fireworks Mfg.Co., Ltd.             POLLACKA
Changsha Longtai Fireworks Manufacture Co. Ltd.            POLLACKA
Changsha Topsun Fireworks Co., Ltd.                             POLLACKA
China DIY Marketing Ltd.                                             POLLACKA
China DIY Marketing Ltd.                                             POLLACKA


I want to write a query  in Oracle so that each company is assigned to a different person.  and remove POLLACKA

output

Airgas USA, LLC                                                            emp1
Airgas USA, LLC                                                            emp1
An Ping County Fireworks General Factory            emp2
Brothers Pyrotechnics, Inc.                                            emp3
Changsha Huibaijin Fireworks Mfg.Co., Ltd.             emp4
Changsha Longtai Fireworks Manufacture Co. Ltd.            emp5
Changsha Topsun Fireworks Co., Ltd.                             emp6
China DIY Marketing Ltd.                                             emp7
China DIY Marketing Ltd.                                             emp7
0
mikensu12
Asked:
mikensu12
  • 3
  • 2
1 Solution
 
sdstuberCommented:
assigning a distinct employee per row:


update yourtable set assigned_to = 'emp' || rownum
0
 
sdstuberCommented:
or, something that is more easily ported between oracle and access


UPDATE yourtable a
   SET assigned_to      =
           (SELECT 'emp' || (COUNT(DISTINCT b.company) + 1)
              FROM yourtable b
             WHERE b.company < a.company);


this will also assign distinct employees per company, rather than per row
0
 
awking00Commented:
I assume that you meant to show Airgas USA twice above. Also, I assume you really want to update POLLACKA to actual names of employees. Might I also assume there is a table containing the employee names and perhaps employee ids?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
mikensu12Author Commented:
awking00

yes airgas should be twicw and no,  I have no other table.  I was going to assign each employee a number emp1,emp2, ect.  and update again.
0
 
mikensu12Author Commented:
I've requested that this question be deleted for the following reason:

No solutions
0
 
sdstuberCommented:
there are solutions.  

The update in http:#a38784278  produces the requested data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now