0

I have 3 tables that I am currently working with. USER, WORKSTATION and USER_WORKSTATION (basically a temp table).

My

USER table consists of user_id and user_name,

WORKSTATION consists of workstation_id, workstation_name and user_id.

As of right now, the user_id column is empty for all workstations and that is my problem.

I have created a table (imported from excel) USER_WORKSTATION. It consists of only user_names and their corresponding workstation(s). Is there someway that I can write an update query that will update the WORKSTATION table with the user_id found in the USER table based on the user_name and workstation_name combination in the USER_WORKSTATION table? I do not have any constraints currently set up and I'm using Oracle.

Rob Hruska
  • 118,520
  • 32
  • 167
  • 192
gward
  • 3
  • 1
  • @N West I have been trying JOINs but have been coming up empty. update workstation (select lic_user.user_name FROM lic_user JOIN lic_combo ON lic_user.user_name = lic_combo.user_name) set workstation.user_id = lic_user.user_id; – gward Apr 10 '13 at 19:04
  • Sorry, forgot that you have to use a slightly different syntax in oracle. Answer in a minute :-) – N West Apr 10 '13 at 19:08

2 Answers2

1

You can use the MERGE statement for this:

MERGE
INTO    WORKSTATION W1
USING   (SELECT W2.rowid AS rid, U.user_id
           FROM USER_WORKSTATION UW
           JOIN USER U
             ON UW.user_name = U.user_name
           JOIN WORKSTATION W2
             ON UW.workstation_name = W2.workstation_name
        ) q
ON      (W1.rowid = q.rid)
WHEN MATCHED THEN
UPDATE
SET W1.user_id = q.user_id;

You can do an UPDATE as well, it's a little messier.

See Update statement with inner join on Oracle

Community
  • 1
  • 1
N West
  • 6,768
  • 25
  • 40
  • I appreciate your quick assistance @N West but I'm afraid that answer has confused me more. Why the letter abbreviations? – gward Apr 10 '13 at 19:20
  • That's just adding an alias to the tables for ease of use. http://www.w3schools.com/sql/sql_alias.asp . – N West Apr 10 '13 at 19:23
  • Essentially, the MERGE statement takes the Resultset in the USING clause, joins it to the table in the INTO clause, and the `WHEN MATCHED THEN UPDATE` tells the database to update rows that match with the defined `SET` operation. – N West Apr 10 '13 at 19:25
  • @N West: Im receiving this error: ORA-00904: "UW"."WORKSTATION_ID": invalid identifier – gward Apr 10 '13 at 19:32
  • Does USER_WORKSTATION not have workstation id in it? Maybe change the join to join on workstation_name?? – N West Apr 10 '13 at 19:40
  • I just noticied that and made the right change. But then immediately got this error: ORA-30926: unable to get a stable set of rows in the source tables – gward Apr 10 '13 at 19:43
  • Then likely your workstation name / user names aren't unique on the tables. You need to have a unique set. Take a look at this SQLFiddle: http://sqlfiddle.com/#!4/4181ab compared to this one with a workstation who has 2 users: http://sqlfiddle.com/#!4/418fe/1 – N West Apr 10 '13 at 19:48
  • Thank you for all of your assistance on this issue. To not waste anymore of my time I merged what I had in excel and just imported into Oracle. An easy fix this time for what should should have been a simple SQL statement. – gward Apr 11 '13 at 17:01
0

Hi try this plsql block. I Suppose one user one workstation. 

Declare
user_id User.userid%type;
ws_name WorkStationUser.wsname%type;
Cursor WSUSER is select * from WorkStationUser;
user_name WorkStationUser.username%type;

Begin

Open WSUSER;


Loop
 Fetch WSUSER into user_name, ws_name;
 select userid into user_id from USER where username=user_name;
 select wsname into ws_name from WORKSTATIONUSER where username=user_name;
 update WORKSTATION set userid=user_id whrer wsname=ws_name;
 EXIT WHEN WSUSER%notfound;
END LOOP;
close WSUSER;
END;
navneetpandey
  • 1
  • Please don't use cursors if a simple SQL statement works!!! Set-based processing is what RDBMS's do best! – N West Apr 10 '13 at 19:26