Results 1 to 11 of 11

Thread: Omar

  1. #1
    Join Date
    Feb 2006
    Posts
    59

    Unanswered: Omar

    hello,
    i am having a problem regarding update statement. i have two tables client and Par . table par has Client_code, Balance and Date columns while client table has client_code and bal and many other client related fields(the bal column was recently added and is blank. what i want to do is to update the bal column in the clients table and the values should be that in PAR table.. i executed this query but it gives me an error "subquery returns more then one row etc". query is .. update client set client.bal =(select balance from par where par.client_code=client_code);

    its not workin can anybody help me with the complete query.

    many thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, one (or more) CLIENT(s) has more than one record in the PAR table. Therefore, you'll have to restrict the query by adding another condition in the WHERE clause or, if possible, use one of aggregate functions (MAX or AVG, for example).

    Something like this:
    Code:
    UPDATE client c SET
    c.bal = (SELECT p.balance
             FROM par p
             WHERE p.client_code = c.client_code
               AND p.balance_date = (SELECT MAX(p1.balance_date) 
                                     FROM par p1
                                     WHERE p1.client_code = p.client_Code
                                    )
            );
    or this
    Code:
    UPDATE client c SET
    c.bal = (SELECT AVG(p.balance)
             FROM par p
             WHERE p.client_code = c.client_code
            );

  3. #3
    Join Date
    Feb 2006
    Posts
    59
    my both tables have distinct records (almost 5000). the balance is already calculated for each client.. all i want is to pick the balance from here and place it in CLIENT table bal field

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, if you're right then I must be wrong. However, I'd suggest you to check once again whether every client has only one record in the PAR table.

    Here is an example: CLIENT table has two records (clients 1 and 2). PAR table has two records for client 1 and one record for client 2:
    Code:
    SQL> select * From client;
    
    CLIENT_CODE        BAL
    ----------- ----------
              1
              2
    
    SQL> select * from par;
    
    CLIENT_CODE    BALANCE
    ----------- ----------
              1       1000
              1       1500
              2       2000
    
    SQL> update client c set
      2  c.bal = (select p.balance from par p
      3           where p.client_code = c.client_code);
    c.bal = (select p.balance from par p
             *
    ERROR at line 2:
    ORA-01427: single-row subquery returns more than one row
    
    -- DELETING ONE RECORD THAT BELONGS TO CLIENT 1
    SQL> delete from par where client_code = 1 and balance = 1500;
    
    1 row deleted.
    
    SQL> update client c set
      2  c.bal = (select p.balance from par p
      3           where p.client_code = c.client_Code);
    
    2 rows updated.
    
    SQL> select * From client;
    
    CLIENT_CODE        BAL
    ----------- ----------
              1       1000
              2       2000
    
    SQL>
    Now, returning deleted record and using the AVG function:
    Code:
    SQL> insert into par values (1, 3000);
    
    1 row created.
    
    SQL> select * From par;
    
    CLIENT_CODE    BALANCE
    ----------- ----------
              1       1000
              2       2000
              1       3000
    
    SQL> update client c set
      2  c.bal = (select avg(p.balance) from par p
      3           where p.client_code = c.client_code);
    
    2 rows updated.
    
    SQL> select * From client;
    
    CLIENT_CODE        BAL
    ----------- ----------
              1       2000
              2       2000
    
    SQL>
    A query which will check multiple records for clients is this:
    Code:
    SQL> select p.client_code from par p
      2  group by p.client_code
      3  having count(*) > 1;
    
    CLIENT_CODE
    -----------
              1
    
    SQL>
    Was this helpful?

  5. #5
    Join Date
    Feb 2006
    Posts
    59
    nope .. let me explain u . the PAR table contains the total balances of all clients. there is not duplicate client code. just final info that this client has this balance in his account... in simple i just want to copy the balance column from the PAR table to the bal column in the CLIENT table with respect to its client code.. plz help me i have to do this soon

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, I know what you want. And yes, probably you don't have multiple records.

    There is an error in the subquery you wrote; it is a bad habit using column names without table aliases. In your query, "WHERE par.client_code = client_code" it, actually, says that Oracle should select records from the PAR table where "par.client_code = par.client_code".

    Be more careful when using tables with same column names and use table aliases. Solution is probably this (I'm rewriting the query from the post #4):
    Code:
    SQL> update client c set
      2  c.bal = (select p.balance from par p
      3           where p.client_code = c.client_code);

  7. #7
    Join Date
    Feb 2006
    Posts
    59
    alrite it worked thankyou soooooooo much

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No problem

  9. #9
    Join Date
    Feb 2006
    Posts
    59
    y were u emphasizing on table aliases

  10. #10
    Join Date
    Feb 2006
    Posts
    59
    did u actually created these tables for me , just for solving this issue

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Although in some cases use of table aliases is not necessary, there ARE cases - just like yours - which require use of either full table names or their aliases.

    If you do not use any of those, the query won't work - that happened to you.

    The other reason is the fact that, as the time goes by, you will forget which column belongs to which table. Not to mention problems some other developer might encounter trying to debug your query. You'd have to describe all tables involved, search for columns trying to figure out what did you select or join in the query.

    I prefer table aliases - they make a query easier to read (especially when table and/or column names are quite long).

    So: the query could have been written using full table names when referencing table columns (see below) or like the one using table aliases in post #6. I'd suggest you to use any of these.
    Code:
    SQL> update client set
      2  client.bal = (select par.balance from par
      3                where par.client_code = client.client_code);

Posting Permissions

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