creating a temp table from EMP table to update mulitiple rows in the Emp table

[jrdba92]

I would like to know how do you update over 1000 rows in the EMP table from the creating a temp table from the EMP table where you are updating the column name Last_name? Lets say that there are 4000 rows in the EMP table. I create a temp table from emp and I have a excel spread sheet with 1000 new Last names that need to be updated in the EMP table for each row. So the spreadsheet would have ID and Last_name on it so those 1000 ID's will have new last names assigned to it when I load it into the EMP_TEMP table. I have read different posts saying I could use the MERGE statement or use the UPDATE statement but I am not sure on how to write the query so there will be 1000 updated names in the EMP table from the EMP_TEMP table or which statemnt would work the best. I am using Oracle 11g Release 2. If you could provide an example of the a query for the MERGE statment and UPDATE statement I would appreciate much.

 

[SantaMufasa]

Jr,

Could you please post what you have come up with so far for both the UPDATE and the MERGE statements. We can help you from there. I'm certain that the other Tek-Tipsters would feel more comfortable about advising you about your code than providing all of the code from scratch.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[jrdba92]

I am sorry about that. I havent come up with anything. Still researching. Some of the examaples I have seen I am still scratching my head on. Its not all that clear.

 

[Andrzejek]

Let's say this is your EMP table:[pre]
ID Last_name
1 Brown
2 White
3 Yellow
4 Brown
5 White
6 Brown[/pre]

Without going into your TEMP table, how will you know which ID/Names to update?
Would you want to update ALL 'Brown' to 'Smith'?
Or update only 'Brown' with ID of 4 to 'Smith'?

Like Santa says, you need to provide more information. Maybe not 'how' to do it, but 'what needs to happen'. You may not need TEMP table...

Have fun.

---- Andy

 

[SantaMufasa]

I am still scratching my head...Its not all that clear.

Then just provide us with pseudocode (i.e., don't worry about syntax...simply assert code that you believe would be reasonable logically without concern for syntax errors).

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[jrdba92]

ok guys this is what I got:

UPDATE EMP
SET LAST_NAME =
(SELECT LAST_NAME FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID);

It works but it doesnt work. What happened was it updated all 1000 last names to the new last names from EMP_TEMP. SO then I did this:

UPDATE EMP
SET LAST_NAME =
(SELECT LAST_NAME FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID)
WHERE ID = 50;
1 row updated

but I need to be able to update rows that have different ID's. So If I need to update ID's 50,60,70,80,90,100,110,1120,130 with new LAST_NAMES assigned to them or any other column....etc how do I do that?

 

[jrdba92]

Ok guys I got it figured out. I found a query on the internet and so I just plugged in what I am trying to do:


UPDATE EMP
SET LAST_NAME =
(SELECT LAST_NAME FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID)
WHERE EXISTS
(SELECT LAST_NAME FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID);

I had updated 4 rows of DATA into EMP_TEMP with 4 new last_names for 4 different IDs and the LAST_NAME updated to the EMP table for those 4 ID's. So for the 4 different ID's I had 4 different new last_names for them.

 

[SantaMufasa]

Cool, Jr. Good investigation.

So that you will know for future use of EXISTS, it doesn't really matter what expression you place in the EXISTS SELECT; you can even use NULL and it will still work properly:

UPDATE EMP
SET LAST_NAME =
(SELECT LAST_NAME FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID)
WHERE EXISTS
(SELECT NULL FROM EMP_TEMP
WHERE EMP.ID = EMP_TEMP.ID);

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ChrisHunt]

Here's a way to do it using MERGE:

MERGE INTO emp e
USING emp_temp t
ON (e.id = t.id)
WHEN MATCHED THEN UPDATE
SET e.last_name = t.last_name

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ChrisHunt]

I think this should work too:

UPDATE (SELECT e.*,
               t.last_name new_name
        FROM   emp e,
               emp_temp t
        WHERE  e.id = t.id) x
SET x.last_name = x.new_name

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jrdba92]

Thanks guys for the extra info I really appreciate but I do have another question, What if i wanted to update multiple columns using the MERGE statement. For instance I want to update last_name column and phone_number? Would I just add another SET clause in with it?

 

[jrdba92]

I have another issue. I have to take the data from my EMP_TEMP and insert that data into another table but that table has three extra columns: Middle_name, Birth_date, Email. I am not sure on how to create an INSERT statement to populate that table. So I am UPDATING THE EMP TABLE FROM THE EMP_TEMP TABLE and now I have to INSERT the values from the EMP_TEMP to D_EMP.

 

[ChrisHunt]

To update more than one column in a MERGE statement, the syntax is similar to an UPDATE statement:

MERGE INTO emp e
USING emp_temp t
ON (e.id = t.id)
WHEN MATCHED THEN UPDATE
SET e.last_name = t.last_name,
    e.phone_number = t.phone_number

How you insert into the table with the extra columns depends on how you want to treat them. If you just want to leave them NULL (assuming they aren't defined as NOT NULL fields) you can do this:

INSERT INTO emp e
      (id,last_name,phone_number)  -- Note that I only list the columns I want to insert into,
                                   -- ignoring the extra three
SELECT id,last_name,phone_number
FROM   emp_temp

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jrdba92]

Sorry to get back to you guys. I have been working on this and finally figured it out but I see once again you guys came through. Chris the above statement you provided for the insert is what I have found also. But what I failed to mention is I am doing a joins statement because I have 3 tables. EMP, EMP_TEM and D_TEMP. I hav to take the orginal data from EMP and insert it into D_EMP before I take the changed data from EMP_TEMP and update that data in EMP. ADDRESS, MIDDLE_INITIAL, MAIDEN_NAME will be the three extra values I am putting into D_EMP.

INSERT INTO D_EMP
(ID, LAST_NAME, FIRST_NAME, EMAIL, ADDRESS, MIDDLE_INITIAL, MAIDEN_NAME)
(SELECT A.ID, A.LAST_NAME, A.FIRST_NAME, A.EMAIL, A.ADDRESS, '12345 BULLDOG LANE', 'J', 'PENSKE' --I hardcoded the three values
FROM EMP A, EMP_TEMP B
WHERE A.ID = B.ID);

4 rows inserted.

If you have another way of doing this could you please share.

 

[jrdba92]

I am having a major issue with the update using a spreadsheet. What I have discovered when I change data in the last_name columns to a new last name it updates just fine. But there are some last names I dont change and when I leave them blank on the spreadsheet and upload the spreadsheet into EMP_TEMP Oracle assigns a null value to it. Well the problem is when I do my update statement from EMP_TEMP to EMP. I am noticing that those last_names I left blank on my spreadsheet gets updated to a null value in the EMP table when I do my update statement and I dont want that so How do I get around that?? So If I have 20 last_names and I want to update 10 of them but the other 10 I leave blank on the spreadsheet. How do I write my update statement to where it shows 10 names are being updated in EMP and the other 10 names are still the same in EMP and not assigned NULL to those unchanged 10 names?

 

[SantaMufasa]

The rule of thumb is that when your SQL affects too many or too few rows, it is the fault of your WHERE clause (including JOIN clauses).

To resolve your issue, include a filter to exclude rows where the trimmed new value is null, for example:

...and trim(<incoming value>) is not null...

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[jrdba92]

Thanks but what about multiple columns because I will be updating muliple columns