Results 1 to 8 of 8

Thread: Update

  1. #1
    Join Date
    Aug 2010
    Posts
    21

    Unanswered: Update

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Does the sales is another table than product?

    If so, please try:
    Code:
    UPDATE product p
       SET pro_qty = pro_qty - 1
     WHERE EXISTS
           (SELECT 0
              FROM sales s
             WHERE s.pro_id = p.pro_id
           )
    ;

  3. #3
    Join Date
    Aug 2010
    Posts
    21
    hi . when first row add this update work exactly but when another row add all decrement by one

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What rows of product do you want to update?

    but i want when pro_id = sales.pro_id then decrement by one
    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.

  5. #5
    Join Date
    Aug 2010
    Posts
    21
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    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?

  7. #7
    Join Date
    Aug 2010
    Posts
    21
    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)
    );

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by calamaris View Post
    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
    UPDATE works on a single table only. What your query above is doing is the following:
    update product sales
    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.

    set pro_qty = pro_qty -1
    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?)

    where pro_id = sales.pro_id;
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •