Solved

assign based on changing row

Posted on 2013-01-16
8
435 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
8 Comments
 
LVL 73

Expert Comment

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


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

Accepted Solution

by:
sdstuber earned 500 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 31

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 73

Expert Comment

by:sdstuber
ID: 38793848
there are solutions.  

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now