Home » SQL & PL/SQL » SQL & PL/SQL » Update Statement
Update Statement [message #238411] Thu, 17 May 2007 21:30 Go to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

This is my code:
Prompt Order is now ---> Closed

select ' Date Closed: ' ||rtrim(sysdate)||'' from dual;
select ' New Quantity in Stock: ' ||rtrim(Stockqty+Orderqty)||''
	from Product, OrderDetail
	where Product.Inum= OrderDetail.Inum
		and Onum='&vOnum';
commit;

update OrderDetail set Rdate=sysdate where Onum='&vOnum';
update OrderDetail set Ernum='&vErnum' where Onum='&vOnum';
update OrderDetail set OrderStatus= 'Closed' where Onum='&vOnum';
commit;
update Product
	set Stockqty= (select Stockqty+Orderqty
				from Product, OrderDetail
				where Product.Inum= OrderDetail.Inum
					and Onum='&vOnum')
	where exists
		(select Stockqty+Orderqty
				from Product, OrderDetail
				where Product.Inum= OrderDetail.Inum
					and Onum='&vOnum');
			
commit;


And this is excuse from SQL when I run it:
SQL> select ' Date Closed: ' ||rtrim(sysdate)||'' from dual;

 Date Closed: 17-5¤ -07

1 row selected.

SQL> select ' New Quantity in Stock: ' ||rtrim(Stockqty+Orderqty)||''
  2          from Product, OrderDetail
  3          where Product.Inum= OrderDetail.Inum
  4                  and Onum='&vOnum';
old   4:                and Onum='&vOnum'
new   4:                and Onum='1000'

 New Quantity in Stock: 280

1 row selected.

SQL> commit;

Commit complete.

SQL> 
SQL> update OrderDetail set Rdate=sysdate where Onum='&vOnum';
old   1: update OrderDetail set Rdate=sysdate where Onum='&vOnum'
new   1: update OrderDetail set Rdate=sysdate where Onum='1000'

1 row updated.

SQL> update OrderDetail set Ernum='&vErnum' where Onum='&vOnum';
old   1: update OrderDetail set Ernum='&vErnum' where Onum='&vOnum'
new   1: update OrderDetail set Ernum='E4' where Onum='1000'

1 row updated.

SQL> update OrderDetail set OrderStatus= 'Closed' where Onum='&vOnum';
old   1: update OrderDetail set OrderStatus= 'Closed' where Onum='&vOnum'
new   1: update OrderDetail set OrderStatus= 'Closed' where Onum='1000'

1 row updated.

SQL> commit;

Commit complete.

SQL> update Product
  2          set Stockqty= (select Stockqty+Orderqty
  3                                  from Product, OrderDetail
  4                                  where Product.Inum= OrderDetail.Inum
  5                                          and Onum='&vOnum')
  6          where exists
  7                  (select Stockqty+Orderqty
  8                                  from Product, OrderDetail
  9                                  where Product.Inum= OrderDetail.Inum
 10                                          and Onum='&vOnum');
old   5:                                        and Onum='&vOnum')
new   5:                                        and Onum='1000')
old  10:                                        and Onum='&vOnum')
new  10:                                        and Onum='1000')

5 rows updated.

SQL> 
SQL> commit;


Since the Orderqty is in OrderDetail table.
- Stockqty is in Product table.

I want to update a new stock after I receive the order into the Product table. I wants only 1 records update, not all the records in the Product table. However, I always display all records updated instead of 1. I did try the simple update statment too, but it did not work.
Re: Update Statement [message #238429 is a reply to message #238411] Fri, 18 May 2007 00:26 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

exists (select Stockqty+Orderqty
              from Product, OrderDetail
              where BLAH=BLAH)


is always true. Therefore you update all records in product.
Try

update Product p
set Stockqty= (select Stockqty+Orderqty
               from Product, OrderDetail
               where Product.Inum= OrderDetail.Inum
               and Onum='&vOnum')
where p.inum in (select od.inum 
                 from OrderDetail od
                 where od.Onum='&vOnum');

Use =, when you're sure always only one record is returned from OrderDetail.

Regards,

Arian
Re: Update Statement [message #238431 is a reply to message #238429] Fri, 18 May 2007 00:46 Go to previous message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Thanks Alien.
I totally for got abt the in. always looks for the =

Thanks a lot.

Sincerely,

Vickie
Previous Topic: how to search a value in all tables
Next Topic: PL-SQL With Linux
Goto Forum:
  


Current Time: Sat Dec 10 16:26:39 CST 2016

Total time taken to generate the page: 0.11360 seconds