Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: Returning Updated Data

    Hi, it's me again.

    Basically one of my columns needs to be updated (monthly_fees) within my m_membership_type table. The monthly fee needs to rise by 10%.

    I already have the code for this and it work as shown;

    UPDATE M_MEMBERSHIP_TYPE SET MONTHLY_FEE = MONTHLY_FEE*1.1;

    What I want is a statement that outputs the current monthly fee AND the updated monthly fee.

    I found out that I need to use "return" to output updated results, but I cannot find any clear examples to aid me in my own scenario. As well as how to output the current monthly_fee.

    In addition to this, I will also need to output columns from another table, but I want to leave this for after I get the update-return working.

    Thanks.
    Still learning..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select monthly_fee, monthly_fee*1.1 from m_membership_type;
    update m_membership_type set monthly_fee = monthly_fee*1.1;
    commit;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2013
    Posts
    36
    invalid character error? :/ All the column/table names are correct so I don't understand.
    Still learning..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't know what you actually entered, but Oracle apparently did not like it.

    Is COPY & PASTE broken for you?

    it works OK for me.
    Code:
    SQL> select ename, sal, (sal*1.1) raise from emp;
    
    ENAME             SAL      RAISE
    ---------- ---------- ----------
    SMITH             800        880
    ALLEN            1600       1760
    WARD             1250       1375
    JONES            2975     3272.5
    MARTIN           1250       1375
    BLAKE            2850       3135
    CLARK            2450       2695
    SCOTT            3000       3300
    KING             5000       5500
    TURNER           1500       1650
    ADAMS            1100       1210
    
    ENAME             SAL      RAISE
    ---------- ---------- ----------
    JAMES             950       1045
    FORD             3000       3300
    MILLER           1300       1430
    
    14 rows selected.
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2013
    Posts
    36
    My mistake, I copied all of your code when I was only supposed to use the top line, it works now.

    This is the confusing part, I now need to, in addition to the current code, list down all the customers, their current monthly fee and the new proposed fee. So I need a join between my m_membership_type table & the m_customer table.

    Seeing as I am not using query builder for obvious reasons, which kind of join should I be using here? I tried to write out a inner join below but it obviously failed as I am experimenting.

    SELECT monthly_fee, monthly_fee*1.1

    FROM m_membership_type;

    INNER JOIN m_customer

    ON m_membeship_type.monthly_fee = m_customer.customer_id;
    Still learning..

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ON m_membeship_type.monthly_fee = m_customer.customer_id;
    HUH?

    when or why would MONTHLY_FEE ever equal CUSTOMER_ID??????????????

    Realize we don't know what columns are in which table unless & until you show us what you have
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2013
    Posts
    36
    My tables;

    http://i39.tinypic.com/6pcl8w.png

    Because I need to list all customers (customer id, customer name etc) from the m_customer table with their current monthly fee and the new fee.
    Still learning..

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would helpful if you posted CREATE TABLE statements for both tables involved.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Oct 2013
    Posts
    36
    M_CUSTOMER

    CREATE TABLE "M_CUSTOMER"
    ( "CUSTOMER_ID" VARCHAR2(10),
    "LAST_NAME" VARCHAR2(20),
    "FIRST_NAME" VARCHAR2(20),
    "D_O_B" VARCHAR2(11),
    "MEMBERSHIP_TYPE_CODE" NUMBER NOT NULL ENABLE,
    "COUNTRY_OF_RESIDENCE" VARCHAR2(20),
    CONSTRAINT "M_CUSTOMER_DETAILS_PK" PRIMARY KEY ("CUSTOMER_ID") ENABLE
    )
    /
    ALTER TABLE "M_CUSTOMER" ADD CONSTRAINT "M_CUSTOMER_CON" FOREIGN KEY ("MEMBERSHIP_TYPE_CODE")
    REFERENCES "M_MEMBERSHIP_TYPE" ("MEMBERSHIP_TYPE_CODE") ENABLE
    /

    CREATE OR REPLACE TRIGGER "BI_M_CUSTOMER_DETAILS"
    before insert on "M_CUSTOMER"
    for each row
    begin
    if :NEW."CUSTOMER_ID" is null then
    select "M_CUSTOMER_DETAILS_SEQ".nextval into :NEW."CUSTOMER_ID" from dual;
    end if;
    end;

    /
    ALTER TRIGGER "BI_M_CUSTOMER_DETAILS" ENABLE
    /



    M_MEMBERSHIP_TYPE

    CREATE TABLE "M_MEMBERSHIP_TYPE"
    ( "MEMBERSHIP_TYPE_CODE" NUMBER,
    "MEMBERSHIP_TYPE" VARCHAR2(10),
    "MONTHLY_FEE" VARCHAR2(5),
    "MAX_DOWNLOADS_MONTHLY" NUMBER,
    CONSTRAINT "M_MEMBERSHIP_TYPE_PK" PRIMARY KEY ("MEMBERSHIP_TYPE_CODE") ENABLE
    )
    /

    CREATE OR REPLACE TRIGGER "BI_M_MEMBERSHIP_TYPE"
    before insert on "M_MEMBERSHIP_TYPE"
    for each row
    begin
    if :NEW."MEMBERSHIP_TYPE_CODE" is null then
    select "M_MEMBERSHIP_TYPE_SEQ".nextval into :NEW."MEMBERSHIP_TYPE_CODE" from dual;
    end if;
    end;

    /
    ALTER TRIGGER "BI_M_MEMBERSHIP_TYPE" ENABLE
    /
    Still learning..

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select mc.customer_id,mmt.monthly_fee,mmt.monthly_fee*1.1
    from M_CUSTOMER MC, M_MEMBERSHIP_TYPE MMT
    where mc.MEMBERSHIP_TYPE_CODE = mmt.MEMBERSHIP_TYPE_CODE;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by anacedent View Post
    select mc.customer_id,mmt.monthly_fee,mmt.monthly_fee*1.1
    from M_CUSTOMER MC, M_MEMBERSHIP_TYPE MMT
    where mc.MEMBERSHIP_TYPE_CODE = mmt.MEMBERSHIP_TYPE_CODE;
    Works perfect! Now to understand your code..but I think I understand the logic of it.

    Thank you.
    Still learning..

Posting Permissions

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