| How to update data from backup table [message #600287] |
Mon, 04 November 2013 12:28  |
 |
chary12345
Messages: 5
Registered: November 2013
Location: Pune
|
Junior Member |
|
|
Hi,
I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employess.
I created backup of this table and name of backup table is empbackup.
Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table.
Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?
Please help.
Regrds,
Chary
|
|
|
|
| Re: How to update data from backup table [message #600292 is a reply to message #600287] |
Mon, 04 November 2013 12:45  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
You need to be a bit careful with your words. In the Oracle environment, a backup is a copy of a file. You seem to be using the word to refer to a copy of a table. Similarly, you do not mean "restore", because it is impossible to restore column. You restore only a file.
I think you mean "update". In which case, write an UPDATE statement that sets the EMPSAL column of EMPLOYEE to the value of EMPSAL in EMPBACKUP, where the EMPID columns are equal. Have a go, and come back with any questions - or with your solution.
|
|
|
|
|
|
|
|
| Re: How to update data from backup table [message #600305 is a reply to message #600301] |
Mon, 04 November 2013 14:49 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
chary12345 wrote on Mon, 04 November 2013 20:22Hi John,
Many thanks for the corrections.
i tried to use below query
update employee set employee.empsal=empbackup.empsal where employee.empid=empbackup.empid;
this doesn't work.
In future, please remember that "doesn't work" is not an Oracle error message.
You have tried to solve the problem with a join implemented by a predicate but, as you have found, you can't do that without a FROM clause. For this problem, you need to think about using a sub-query, where you can use a join.
And it is time to do the moderator thing:
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
|
|
|
|
|
|
|
|
| Re: How to update data from backup table [message #600332 is a reply to message #600329] |
Tue, 05 November 2013 00:09 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
What is your exact requirement? You want the employee table the way it was before the updates and inserts, am I right? It means, you want the employee table to have same data as that of EMPBACKUP table? Also, you want to keep both the tables in DB. Please confirm.
|
|
|
|
|
|
| Re: How to update data from backup table [message #600338 is a reply to message #600335] |
Tue, 05 November 2013 01:00 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
chary12345 wrote on Tue, 05 November 2013 12:05Now i want to update salary of old employees which were there before creating backup and make it same as it is in empbackup table.
So what about Blackswan's suggestion.
BlackSwan wrote on Tue, 05 November 2013 02:16>update employee set employee.empsal=empbackup.empsal where employee.empid=empbackup.empid;
>this doesn't work.
of course it does not work since UPDATE knows nothing about EMPBACKUP table when working against EMPLOYEE table.
SELECT EMPSAL FROM EMPBACKUP .......
You need to return the rows from EMPBACKUP to the update set statement.
UPDATE EMPLOYEE A
SET A.EMPSAL =
(SELECT B.EMPSAL FROM EMPBACKUP B WHERE A.EMPID = B.EMPID); |
|
|
|
| Re: How to update data from backup table [message #600343 is a reply to message #600329] |
Tue, 05 November 2013 01:37 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
chary12345 wrote on Tue, 05 November 2013 06:06I tried using sub-query and join too but couldn't make it work.
Can any one please provide me the working query.
First, "couldn't make it work" is not an Oracle error messge. You need to show what you did, and what happened. Use copy/paste, enclosed in [code] tags.
Second, your question looks like a college homework assignment. Giving you the answer would compromise your learning, so no-one should do that. Hints, yes; solutions, no.
Third, Lalit has broken the rules by giving you an attempt at a solution. However, you need to test it, and in particular think about what will happen when the subquery returns either no rows or more than one row.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
