Solved

assign based on changing row

Posted on 2013-01-16
8
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 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 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 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