Updating two tables in oracle

6854933580_2c8b688306_z

The above query will give you many, many rows that look identical; however, if you replace the column list with an asterisk (*) and rerun the query, you’ll notice that the records aren’t exactly identical, each has one column different. PHONE_ID; in our select list) exist in two or more of our tables, we must always tell Oracle which one we are referring to every time we use that column name. Excluding all end-dated rows now would give us the wrong result. But we don’t want to hard-code the date into our query, in case we want to rerun the query next week. What we have here is a Cartesian Product, and you’ve probably already guessed that it has something to do with our missing Where clause. Whenever we don’t tell Oracle how our tables are related to each other it simply joins every record in every table to every record in every other table. We do this by preceding the column name with the table name. In our previous articles we acquainted ourselves with our bricks; now it’s time to build. The syntax is as follows: Run the query and notice how the resultset now contains all your friends, including those who do not have a phone number.

But what if we wanted to see a full list of our friends with a null if they do not have a phone number?If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to… In other words, we can say, we want to see all of our friends (all records in friend_name), and we don’t mind seeing nulls whenever they don’t have a phone number.Continue Reading → Part 1, Part 2, Part 3 A wise man* once said: To build the Great Wall of China, you must start with a brick. There are two types of outer joins; a left outer join allows nulls in the second table in our join, while a right outer join allows nulls in the first table (while showing all records from the table on the right).SQL select * from new_emp; ID FNAME LNAME START_DAT END_DATE SALARY CITY ---- ---------- ---------- --------- --------- ---------- ---------- DESCRIPTION --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1235.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6662.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6545.78 Vancouver Tester ID FNAME LNAME START_DAT END_DATE SALARY CITY ---- ---------- ---------- --------- --------- ---------- ---------- DESCRIPTION --------------- 04 Celia Rice 24-OCT-82 21-APR-99 2345.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2335.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4323.78 New York Tester ID FNAME LNAME START_DAT END_DATE SALARY CITY ---- ---------- ---------- --------- --------- ---------- ---------- DESCRIPTION --------------- 07 David Larry 31-DEC-90 12-FEB-98 7898.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1233.78 Vancouver Tester 8 rows selected.it solved my problem to some i want to know can we update multiple values at the same time using a single querry. Take a look at this example:update ( select profit_bonus, merit_bonus from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'N' ) t set t.profit_bonus = 0, t.merit_bonus = 0; Hi David, This time where the employee is eligible for a bonus :-)update ( select b.bonus, e.salary from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'Y' ) t set t.bonus = 0.1 * t.salary Hope this helps!Also, I don't think you can update any part of the key using this method. Hi, I have a same problem while updating the table.

You must have an account to comment. Please register or login here!