Hi every one

i have a problem with update s when use this statement
update product sales
set pro_qty = pro_qty -1
where pro_id = sales.pro_id;

after execute this commands all pro_qty in table product decrement by 1

but i want when pro_id = sales.pro_id then decrement by one

Does the sales is another table than product?

If so, please try:

hi . when first row add this update work exactly but when another row add all decrement by one

What rows of product do you want to update?

From this statement, I thought that you want to update all rows of product which have corresponding row(s) in sales.

Please show sample data of product and sales and the desired result.

select * from product

PRO_ID PRO_WEIGHT PRO_QTY
------ -------------------- -------
p1h1 10
p1h2 10
p1c1 10
p1a1 10
insert into sales values('s1',1.34,'2010-8-1','m1','p1h1','h1');
PRO_ID PRO_QTY
---------- -----------
p1h1 9
p1h2 10
p1c1 10
p1a1 10
insert into sales values ('s2',1.34,'2010-8-1','m1','p1h2','h2');
PRO_ID PRO_QTY
---------- -----------
p1h1 8
p1h2 9
p1c1 10
p1a1 10
i use update statement with trigger after insert all pro_num from product decrement by 1

How to know(or relate) the row prod_id = p1h1 in product from the inserted row ('s2',1.34,'2010-8-1','m1','p1h2','h2') of sales?

this is sales table

create table sales(
sal_id char(10) not null primary key,
sal_price decimal(4,2),
sal_date date,
man_id char(10),
pro_id char(10),
hob_id char(10),
foreign key (man_id) references Manufacture(man_id) On delete restrict,
foreign key (pro_id) references product(pro_id) on delete restrict,
foreign key (hob_id) references hobbyist(hob_id) on delete restrict
);

create table product(
pro_id char(10) not null primary key,
pro_name varchar(20) check (pro_name in('airplane','helicopter','boat','car')),
pro_model varchar(50),
pro_color varchar(10),
pro_type char(11) check (pro_type in ('electronic','gas')),
pro_enginesize int check (pro_enginesize in (1,2,3,4)),
pro_speed KM,
pro_weight KG,
pro_qty int check (pro_qty >1)
);

UPDATE works on a single table only. What your query above is doing is the following:
Apply some updates to table "product" and use the correlation name "sales" in any predicates and expressions referring to that table. Note that "sales" does NOT identify the "sales" table you may have in the same or a different schema.

Change the "pro_qty" column. (Btw, why do you use such cryptic abbreviations? "product_quantity" is a perfectly fine name, don't you think? Or do you just want to make maintenance more complicated?)

The first expression "pro_id" refers to the table being update, i.e. table "products". The second expression uses the correlation name "sales", which happens to refer to table "products" as well. So all rows will qualify here (except those rows where pro_id is NULL).