[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

assign based on changing row

Posted on 2013-01-16
8
Medium Priority
?
480 Views
Last Modified: 2013-01-21
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
Comment
Question by:mikensu12
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38784253
assigning a distinct employee per row:


update yourtable set assigned_to = 'emp' || rownum
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38784278
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
 
LVL 32

Expert Comment

by:awking00
ID: 38784316
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:mikensu12
ID: 38785489
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
 

Author Comment

by:mikensu12
ID: 38793847
I've requested that this question be deleted for the following reason:

No solutions
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38793848
there are solutions.  

The update in http:#a38784278  produces the requested data.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question