Home » SQL & PL/SQL » SQL & PL/SQL » Please help with multiple update
Please help with multiple update [message #602009] Thu, 28 November 2013 14:50 Go to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
I am trying to change the query but the request is somehow complicated:
I have a table with 3 columns:
Start date, seqID, Name
07/07/2013 1 Name1
07/09/2013 2 Name2
07/11/2013 3 Name3
07/15/2013 4 Name4
07/20/2013 5 Name5
If start date 07/15/2013 is changed to 07/10/2013 then seq ID 4 becomes 3 and the row becomes after 07/09/2013, seq ID 2
If Start Date 07/09/2013 is changed to 07/16/2013 then the row is re-sequenced as number 4 and moved to above 07/20/2013: The update would be as follows:
Start date, seqID, Name
07/07/2013 1 Name1
07/11/2013 2 Name3
07/15/2013 3 Name4
07/16/2013 4 Name2
07/20/2013 5 Name5

How can I re-sequence the seqID when start date is changed?
Thanks
Re: Please help with multiple update [message #602012 is a reply to message #602009] Thu, 28 November 2013 16:08 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Get rid of the sequence id, it is worthless. Derive your 'sequence' at runtime.
Re: Please help with multiple update [message #602013 is a reply to message #602009] Thu, 28 November 2013 18:46 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Depending on how duplicates (entries with the same start date) are numbered at run time (as pablolee pointed out) I think one of the analytic functions row_number or dense_rank may fulfil your requirement, for example by creating a view.

By the way, what was the purpose of this sequence for which you defined a column?
Re: Please help with multiple update [message #602014 is a reply to message #602013] Thu, 28 November 2013 18:48 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Please help with multiple update [message #602015 is a reply to message #602013] Thu, 28 November 2013 18:57 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
Thanks for your replies. Yes there are duplicate dates and are sorted in ascending (HR,MIN). The Sequence ID is the displayed number and is part of the table. When user click Edit to update date then the screen will be refreshed at run time moving the row up or down at the same time the Sequence number is updated. I was wondering about using dense_rank but I don't have that experience besides Java & Dot.Net. The Oracle/SQL in Dot.Net is plain update for the date only and now need to re-sequence the displayed number. Appreciate if you can give me an example using either dense_rank or row_number.
Re: Please help with multiple update [message #602016 is a reply to message #602015] Thu, 28 November 2013 19:48 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
The design/requirement is daft & I won't facilitate or perpetuate this foolishness.

You should NEVER store a computed value within a (static) row.
Every computed value should be selected, computed & displayed as needed.

[Updated on: Thu, 28 November 2013 21:28]

Report message to a moderator

Re: Please help with multiple update [message #602023 is a reply to message #602016] Thu, 28 November 2013 23:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2079
Registered: May 2013
Location: World Wide on the Web
Senior Member
BlackSwan wrote on Fri, 29 November 2013 07:18
You should NEVER store a computed value within a (static) row.
Every computed value should be selected, computed & displayed as needed.


True. But if it is really needed, then for 11g and up, virtual columns could be used. Although, they are some restrictions.
Re: Please help with multiple update [message #602062 is a reply to message #602023] Fri, 29 November 2013 03:29 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Lalit Kumar wrote on Fri, 29 November 2013 06:44
True. But if it is really needed, then for 11g and up, virtual columns could be used. Although, they are some restrictions.

This has nothing to do with Blacksawn's remark as he said:

Blacksawn wrote on Fri, 29 November 2013 02:48
You should NEVER store a computed value within a (static) row.

Virtual column are not being stored on the disk and besides I don't see how in the context of this question you use them in order to provide a numbering like row_number() or dense_rank().
Re: Please help with multiple update [message #602064 is a reply to message #602015] Fri, 29 November 2013 03:32 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
envision2005 wrote on Fri, 29 November 2013 01:57
I was wondering about using dense_rank but I don't have that experience besides Java & Dot.Net.

Have a look at the links I provides where you have working examples. Yet, you don't specify how to numerate when there are duplicates.
Re: Please help with multiple update [message #602099 is a reply to message #602062] Fri, 29 November 2013 06:58 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
I am using Oracle 11g
Will this work:
UPDATE myTable
SET seqID = (SELECT r FROM (SELECT rs1.startDate, rs1.seqID, rs1.name,
ROW_NUMBER() OVER (PARTITION BY rs1.startDate, rs1.seqID, rs1.name
ORDER BY rs1. startDate) AS r
FROM myTable rs1
WHERE rs1.startDate = :DateEntered --07/20/2013
AND rs1.seqID = 2)
WHERE rs1.startDate = startDate AND rs1.seqID = seqID AND rs1.name = name)
WHERE startDate = :DateEntered AND seqID = 2

[Updated on: Fri, 29 November 2013 07:20]

Report message to a moderator

Re: Please help with multiple update [message #602123 is a reply to message #602099] Fri, 29 November 2013 12:18 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
As it was suggested earlier, there is a design problem, the sequence column is not to be stored and the numbering has to be done at run time (for example by creating a view).

[Updated on: Fri, 29 November 2013 12:24]

Report message to a moderator

Re: Please help with multiple update [message #602169 is a reply to message #602099] Sat, 30 November 2013 14:40 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Sure, go for it. Ignore the advice that you have been given. Enjoy the fruits.
Re: Please help with multiple update [message #602171 is a reply to message #602169] Sat, 30 November 2013 15:03 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
Pablolee,
Please don't take it that way. The replies from all above are appreciated and all agreed that the requirement is not correct. This where I need to discuss in the office. Again, I am not Oracle expert to that level and what I got is very tricky problem. No fruits to enjoy but again appreciate the replies.
Re: Please help with multiple update [message #602173 is a reply to message #602171] Sat, 30 November 2013 16:26 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Pablolee,
Please don't take it that way.

What other way should it be taken. Several posts point out that your model is flawed. You post an attempt at a solution that utterly ignores that advice that you were given. I say again, what other way should it be taken?
Quote:
The replies from all above are appreciated and all agreed that the requirement is not correct.
Yet, you chose to ignore that.
Quote:
Again, I am not Oracle expert to that level
That's why you are here, asking advice, no problem with that.
Quote:
and what I got is very tricky problem.
Hmmm, no, you don't. Based on your description, you have a very simple problem, easily solved by using a view, or virtual columns
Quote:
No fruits to enjoy but again appreciate the replies.
I'm not sure what that means, but if you're trying to say that this thread doesn't contain the answer to your problem, then either you are completely wrong, or you have completely misrepresented your problem.
Re: Please help with multiple update [message #602174 is a reply to message #602173] Sat, 30 November 2013 18:03 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
Let me try again to explain the scenario: Screen with gridView. Each row has 2 buttons: Edit & Delete
When user click "Delete" on any row, the row is deleted and the screen is refreshed at run time. Below is the Query:
Update myTable
SET PROJECT_DISPLAY_NBR = PROJECT_DISPLAY_NBR - 1,
UPDATE_DTTM = '16-AUG-2013', UPDATE_USER = 'ANY.NAME'
WHERE PROJECT_DISPLAY_NBR >= '2' AND PROJECT_SEQ_ID = 40819; --"Project_display_Nbr" 2 is row 2 which is selected to delete.

When Editing the row, user is only allowed to change the date in the row. When date is changed then 2 things I need to do: 1) Compare the date of the row changed with the dates of the rest of the rows (up & down). 2) If the date that is updated is less than the above row (for example) then the "project display nbr" is also updated.
Start date, project_Display_nbr, Name
07/07/2013 1 Name1 Top row is non editable
07/09/2013 2 Name2 Edit Delete
07/11/2013 3 Name3 Edit Delete (date is changed to 07/08/2013).
07/15/2013 4 Name4 Edit Delete
07/20/2013 5 Name5 Edit Delete
The new updated row:
Start date, project_Display_nbr, Name
07/07/2013 1 Name1
07/08/2013 2 Name3 Edit Delete (after date is changed, 3 becomes 2).
07/09/2013 3 Name2 Edit Delete (2 becomes 3)
07/15/2013 4 Name4 Edit Delete
07/20/2013 5 Name5 Edit Delete
I haven't used Virtual & View and how they can be used for the above scenario.
Thanks
Re: Please help with multiple update [message #602177 is a reply to message #602174] Sat, 30 November 2013 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
07/07/2013 1 Name1 Top row is non editable
07/09/2013 2 Name2 Edit Delete
07/11/2013 3 Name3 Edit Delete
07/15/2013 4 Name4 Edit Delete
07/20/2013 5 Name5 Edit Delete (date is changed to 07/06/2013).

What should result when change above is made?
Re: Please help with multiple update [message #602193 is a reply to message #602177] Sun, 01 December 2013 03:17 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
If date of last row (or any row) is changed from 07/20/2013 to 07/06/2013 (before top row): This validation is taken care in the code behind. If date is before the top record it will throw error message: Cannot be before 07/07/2013. Again, the top row is non editable.

[Updated on: Sun, 01 December 2013 03:20]

Report message to a moderator

Re: Please help with multiple update [message #602195 is a reply to message #602174] Sun, 01 December 2013 04:55 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
envision2005 wrote on Sun, 01 December 2013 01:03
When Editing the row, user is only allowed to change the date in the row. When date is changed then 2 things I need to do: 1) Compare the date of the row changed with the dates of the rest of the rows (up & down). 2) If the date that is updated is less than the above row (for example) then the "project display nbr" is also updated.

As I suggested earlier, I think a view is what you're looking for. Create a view, that includes all columns from your table needed for printing in your data grid preceded by a row_number()

Based on the sample output you posted, there are three columns

- start_date,
- project_display_nbr (which we told you, this should'nt be stored as a column in your table),
- name

What I suggest is something like this
create or replace view your_datagrid_v as
select start_date,
       row_number() over (order by start_date asc) project_display_nbr,
       name
from   yourTable;


After each comitted update or delete, your .Net code has to do a new select on this view to update properly the order of rows in your data grid.
select start_date          ,
       project_display_nbr ,
       name
from   your_datagrid_v
order by project_display_nbr asc;

[Updated on: Sun, 01 December 2013 07:35]

Report message to a moderator

Re: Please help with multiple update [message #602201 is a reply to message #602195] Sun, 01 December 2013 08:09 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
With create View table, the process will use 3 steps:
1) Create a view and copy the original table to this view before any update.
2) Use view table to update the date based on selection in my dot.net screen
3) Then Select * from view table using order by.
Creating a view table required a system privilege which I don't have. I will need to check with the DBA.
My question: Should I drop table view after the process is completed? I mean create a View each time a new process start.
Thanks and appreciate your suggestion, I need to try this tomorrow in the office
Re: Please help with multiple update [message #602202 is a reply to message #602201] Sun, 01 December 2013 08:25 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2079
Registered: May 2013
Location: World Wide on the Web
Senior Member
envision2005 wrote on Sun, 01 December 2013 19:39
Should I drop table view after the process is completed? I mean create a View each time a new process start.


Why? You need not do that. A view is a logical table, it doesn't contain any data. Only it's base tables will have the data which are already present in your DB.

Read the documentation, Create View

Understand how a view works. And then think why do you want to drop it and create every time?

[Updated on: Fri, 07 March 2014 01:43] by Moderator

Report message to a moderator

Re: Please help with multiple update [message #602204 is a reply to message #602201] Sun, 01 December 2013 08:38 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
envision2005 wrote on Sun, 01 December 2013 15:09
My question: Should I drop table view after the process is completed? I mean create a View each time a new process start.

I'm not sure that you understand how a view works, have a look at the following page of the Database Concepts about views

http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#i20690
Re: Please help with multiple update [message #602205 is a reply to message #602202] Sun, 01 December 2013 09:09 Go to previous messageGo to next message
envision2005
Messages: 9
Registered: November 2013
Junior Member
Got it. Thanks, I haven't used View before. A view contains no data itself. Can only be used on functions, procedures, types, views, or packages - it will not work on tables.
Can also update the records in an SQL view.
Re: Please help with multiple update [message #602206 is a reply to message #602205] Sun, 01 December 2013 09:21 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
envision2005 wrote on Sun, 01 December 2013 16:09
G A view contains no data itself. Can only be used on functions, procedures, types, views, or packages - it will not work on tables.

Are you sure that you really read the view chapter of the Database Concepts on the link that I provided above? ./fa/3314/0/
Re: Please help with multiple update [message #602207 is a reply to message #602205] Sun, 01 December 2013 09:22 Go to previous message
envision2005
Messages: 9
Registered: November 2013
Junior Member
Thanks Dariyoosh, the web site you provided is very helpful with examples.
Previous Topic: Error in reading XML message and inserting data into another DB using DB link
Next Topic: ORA-00935: group function is nested too deeply
Goto Forum:
  


Current Time: Thu Aug 21 21:57:19 CDT 2014

Total time taken to generate the page: 0.09390 seconds