Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question Efficient update operation

    In one of threads of this forumn, I am suggested to employ the following table structure to handle a set of data of one field.

    TABLE01
    id field01

    An example is the followings:

    PERSON_ETHNICITY (as a detail table of a table PERSON)
    id ethnicity
    123 black
    123 asia

    (Mixed race situation)

    SQL statements of insertion and selection are normal with a small twist (a batch statement for insertion). They can be done with one single statement (I use JDBC by the way). When come to updating, I don't see any options other than two statements, delection and insertion are a paire of options.

    Is any more efficitive way to achieve the same purpose?

    Thanks for your input.

    v.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Efficient update operation

    Are you are saying you want to be able to update a person's ethnicity data in a single statement, like "change Joe's ethinicity from (black,asian) to (black,white)"?

    There are probably several ways to achieve this, typically involving some sort of array or "collection". I don't know JDBC, so I'm not sure what it can handle, but I imagine it can handle most Oracle datatypes like nested tables, VARRAYs. You could look into passing a table object as a parameter to a procedure, which then does the required inserts and deletes to the PERSON_ETHNICITY table.

    Or, if you don't want to use objects, you could do something like this:

    In application: call stored procedure update_ethnicity( 123, 'black,white' );

    In stored procedure: parse the second parameter to get the individual values 'black' and 'white'. It could perhaps just delete ALL existing records for the given ID and insert all the new values.

    Whatever approach you take, having the inserts, updates and deletes in a stored procedure (in a package) and calling that from your client app is always a good idea - can reduce network traffic, and means all SQL tuning can be done on the server.

  3. #3
    Join Date
    Oct 2002
    Posts
    37

    Lightbulb

    Hi, Tony,

    Yes, that is what I would like to have: one single standard SQL statement to do the job.

    I also would like to have standard SQL, and avoid using any stored procedures. I am currently using PostgreSQL, and may move to SAP DB one day. I am not aware anything like nested tables in PostgreSQL. I could use the standard array data type. The data type is not good for the selection operation based on my knowledge. The efficiency of selection operation is important in the application.

    JDBC takes all kind of SQL statements with some additional features such as transaction management, batch statements.

    Thanks,

    v.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The only thing I can think of is CASE:

    Code:
    UPDATE PERSON_ETHNICITY
       SET ethnicity = CASE
    		        WHEN ethnicity = 'black' THEN 'black'
    	                WHEN ethnicity = 'asian' THEN 'white'
                       END
     WHERE id = 123
    But again this only works if you know your original and final values, and you are not changing the number of ethnicity values.

    Your best bet would be to do what andrewst says and handle it in your application with several queries. One to DELETE FROM ethnicity WHERE id = 123 then two INSERT INTO ethnicity VALUES( 123, 'black' ), ( 123, 'white' ).
    Thanks,

    Matt

  5. #5
    Join Date
    Nov 2002
    Posts
    2
    v,

    I'm just learning PostgreSQL. Postgre does have an array data type. Could you use that to have all of your ethnicities in one field, then completely replace the field with the 'new' data (black, white)?

    BTW, why don't you want to use stored procedures?

  6. #6
    Join Date
    Oct 2002
    Posts
    37
    The update statement I am using is a generic one. They are not necessary updating the same number of elements. So, the solution is as what I originally thought: to have a pair of deletion and insertion statements. This can be done using a batch statement in JDBC - Not a bad solution.

    Thanks Matt and Andraw for your both helps.

    v.

    Originally posted by MattR
    The only thing I can think of is CASE:

    Code:
    UPDATE PERSON_ETHNICITY
       SET ethnicity = CASE
    		        WHEN ethnicity = 'black' THEN 'black'
    	                WHEN ethnicity = 'asian' THEN 'white'
                       END
     WHERE id = 123
    But again this only works if you know your original and final values, and you are not changing the number of ethnicity values.

    Your best bet would be to do what andrewst says and handle it in your application with several queries. One to DELETE FROM ethnicity WHERE id = 123 then two INSERT INTO ethnicity VALUES( 123, 'black' ), ( 123, 'white' ).

  7. #7
    Join Date
    Oct 2002
    Posts
    37
    In my project, the system performance largely depends on search operations, which is selection operation in the DB. I don't know how to issue a selection statement against array data type (Please let me know if you know one). Although, I can move the selection to the application layer (in fact, that is my first approach), I would like to employ DB approach in the regard for a better performance.


    Originally posted by robin
    v,

    I'm just learning PostgreSQL. Postgre does have an array data type. Could you use that to have all of your ethnicities in one field, then completely replace the field with the 'new' data (black, white)?

    BTW, why don't you want to use stored procedures?

Posting Permissions

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