First table ROOMDB:

Second table RENTALINVOICE:

My update code:

UPDATE ROOMDB
SET 
RENTALBALANCE = (SELECT TOTALDUE
                   FROM RENTALINVOICE
                  WHERE RENTALINVOICE.ROOMNUMBER=ROOMDB.ROOMNUMBER
                    AND INVOICEDATE=SYSDATE) ;

I need to update the totaldue column in ROOMDB with data from RENTALINVOICE though it successfully enters 2,200 to the totaldue column, AT THE SAME TIME IT ALSO WIPES OUT THE REST OF THE RECORDS on this column in ROOMDB.

Everytime I update it, it erases the rest of the records except the roomnumber I specified. Please help.

CodePudding user response：

You basically seem to also updates rows in table roomdb where there is no row in table rentalinvoice and therefore the column rentalbalance will be set to null.

Have a look a following example:

drop table a;
create table a (id number, cost number);
insert into a values (1, 1);
insert into a values (2, 2);

drop table b;
create table b (id number, cost number);
insert into b values (1, 100);

-- updates all rows in a and sets cost to null whenen there is no row in b
update a set cost = (select cost from b where a.id = b.id);
select * from a;

-- only updaes rows in a where there is a row in b
update a set cost = id;
update a set cost = (select cost from b where a.id = b.id) where exists (select 1 from b where a.id = b.id);
select * from a;

CodePudding user response：

When you execute an update command like this Oracle will update all the table rows. For every row Select command will execute with these row values. If the result of select in the update does not find any record will return null. In this situation the RENTALBALANCE column will set null too.

(SELECT TOTALDUE
          FROM RENTALINVOICE
         where WHERE RENTALINVOICE.ROOMNUMBER = ROOMDB.ROOMNUMBER
                 AND INVOICEDATE = SYSDATE)