Solved

assign based on changing row

Posted on 2013-01-16
8
451 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

617 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