Updating two tables in oracle

The inner select does result in 53 rows, and the table being updated DOES have a primary key. No knowing your table structure, my first guess would be that Party.objectid does not have unique constraint on it.

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.

If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé. Notice how, by using a left outer join, we got all the rows in friend_name (the table on the left of the join); change it to a right outer join and see how that changes the output. But we need to add a third table – PHONE_NUMBER – to our query to make it useful. Joining multiple tables in this way is a little like baking a cake; you apply your joins layer by layer.

We’ve guarded against Cartesian joins, we’re using table aliases, and comparing our dates to SYSDATE, but we can use a function named NVL() to tidy up this condition: and (fp.end_date is null or fp.end_date The NVL function will return value if it is not null, but will return replacement_value if it is. When two tables are joined using a simple join (e.g.

The joins we’ve been using thus far are effectively simple inner joins.

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!

PHONE_NUMBER FROM FRIEND_NAME FN, FRIEND_PHONE FP, PHONE_NUMBER PN WHERE FN. To do this I’ll need to tell you about outer joins.

But what if we wanted to see a full list of our friends with a null if they do not have a phone number?

These are short nicknames you can give to tables (to do this you follow the table name immediately with the alias in your From statement).

Let me rewrite our query to illustrate this: SELECT FN.

The syntax for a multi-table select statement is as follows: Run that and see what you get. However, when I run the query, I get 2 rows for Rachel – one for her current phone number and another for her previous one. Of course we can simply add to our column list and look to see which record is current. END_DATE IS NULL” to our Where clause and exclude end-dated rows in that way. Expand the query to display our friends’ addresses: to do this you’ll need to join the FRIEND_ADDRESS and ADDRESS tables.