|
 my boss told me to now i hv to hard code it
|
|
|
|
|
Hi,
I am using Microsoft Access 2002 database.
I want to update one field of Table. I have repetitive values in different fields like. (Book Table field & values below)
bookId 22
pageId 1
PKValue 134-100
Position
The PKValue is unique identifier. One Page can contain number of unique PKValues. That means we can have repetitive bookId and pageId values.
Now if I want to update Position field based on bookId, pageId and PKValue my query needs long loop for updating many Positions of single page.
Can somebody guide me what is solution to get fast update of Position field?
I hope I conveyed my question right.
Thank you very much.
|
|
|
|
|
Hi,
if you want to do anything with databases and are new to it, the only advice I have for you is to buy and study a book on the subject; you just can't improvise a DB app and expect it to work reliably without a solid foundation.
The particular answer would be along these lines:
UPDATE tablename SET fieldname=expression_using_field_values_and_or_constants WHERE field1=value1 AND field2=value2
Googling for SQL update would yield this[^] amongst others.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
Thank you for reply. I will act upon your advice. Sure I will grab a book.
As you mentioned the query. I am already using it. Actual problem is performance. You know this update uses composite key (bookId, pageId, PKValue) to update one single field.
UPDATE [Books] SET [Position] = '1234' WHERE bookId =22 AND pageId =1 AND PKVALUE = '134-100';
Now for example I have to updates 200 position of this pageId = 1 and bookId = 22
I have to call 200 times this Query.
What I can do to just fill once for all for a specific page and then next page and go on.
Thank you.
|
|
|
|
|
vhassan wrote: You know this update uses composite key (bookId, pageId, PKValue) to update one single field.
Let me append: for all the selected records.
vhassan wrote: Now for example I have to updates 200 position of this pageId = 1 and bookId = 22
I have to call 200 times this Query.
If you want the same update to apply to all 200 records, then write your query to update all 200 records and just call it once. You need to write your query to do what you want. Perhaps a book, like Luc Pattyn suggested would be helpful to you.
|
|
|
|
|
Thank you for your replies.
|
|
|
|
|
How about using an update query?
Something like:
UPDATE Table SET Position = Position + 1 WHERE PKValues = '134-100' And pageId = 1 And bookId = 22
|
|
|
|
|
Thank you Avi Berger.
Yes this update works.
Position is not incremental instead it is calculated value.
Please read my reply to Luc Pattyn.
Have a good day.
|
|
|
|
|
That was a sample, not something to be used exactly as written.
vhassan wrote: Position is not incremental instead it is calculated value.
Incrementing is one kind of calculation. I have no way of knowing what calculation you need. You would need write whatever calculations and record selection criteria you actually need.
|
|
|
|
|
I have a stored procedure that creates Pivot/cross tab resultset. for example my data is as follows :
Id, Date, Value
1, 01/12/2009, 100
1, 02/12/2009, 110
2, 01/12/2009, 50
2, 02/12/2009, 55
2, 03/12/2009, 75
3, 02/12/2009, 100
The Stored Procedure would result a resultset as follows :-
Id, 01/12/2009, 02/12,2009, 03/12/2009
1, 100, 110, 0
2, 50, 55, 75
3, 0, 100, o
So far so good.
Now I need to join the results of the stored procedure to a another table joined by Id, either in a View or another Stored Procedure. Can I do this, keeping in mind that the number of columns and the names of the colums are variable. The only constant is the Id column name.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
You have a number of choice on how to transport a resultset from a proc
Turn the proc into a view - always the first choice but rarely possible with a pivot
Use a UDF - also dies on the requirement of dynamic SQL to define the columns
Shove the resultset into an XML variable and pass it back as an out parameter - there are a number of articles on CP on the subject. I have used this once and while it worked I have a strong dislike for XML.
Use a global temp table. This is my default option but it has some drawbacks like using the tempdb, house keeping the tables and race issues if it is a popular proc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. I think I can discount the first three options.
I will investigate the other two options.
Thanks again
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Try turning the procedure into a table-valued function.
|
|
|
|
|
Hi Experts
i am using SQL Server 2005. I encrypted the Stored procedure with the help
of SQL Statment WITH ENCRYPTION But Now it is not open to me.
So any body can tell me how to decrypt it to orginal Text.
Thank u
Dinesh
|
|
|
|
|
|
I have been working on report creation in Business Object XI R2. The report i have created contains aggregated data(summary information) and deals with around 250 GB of database. The database is on SQL Server 2005 standard edition.
I have to show summarized information from 2 years for more than 20 different criteria.
The approach i am following is I am bringing the aggregated information in one DP and all different informations in other 4 DP. And i am using Synchrnization to link DP's to get the required groupings and present in different tab.
I have successfully created the report and refreshing independent DP correctly. But the problem is when we are refreshing the complete report in one go.
I am getting the error "There is not enough memeory to run this program. Please quit one or more programs, then press ok to try again."
Is there any way around to improvise the performance of report and use less memory.
Thanks in Advance
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
|
What's involved in handling mutl-users in a C# WinForms app targeting SQL 2005?
Everything makes sense in someone's mind
|
|
|
|
|
Deploy multiple copies of the client application!
SQL Server is a server based database and as such is multi user by default.
You need to build a client application (in c#) that connects to the database to manipulate the data, the client is single user with multiple deployments!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello guys,
Anyone knows how to script SQL jobs from a sql server database so that i can script them back into another database? I am using Quest Toad For sql server to view the sql jobs on the server.
Regards,
Christian Pace
|
|
|
|
|
For SQL2000 using SQL Server Enterprise Manager:
Right-click on the job, choose for all tasks/Generate SQL Script...
Wout Louwers
|
|
|
|
|
Hi,
I am having 3 tables A,B,C
table A:-
AID date
23 01/25/2010
24 01/25/2010
table B:
BID AID FileName date
1 23 1.doc 01/25/2010 1.00 AM
2 23 2.doc 01/25/2010 1.05 AM
3 23 3.doc 01/25/2010 1.06 AM
4 24 4.doc 01/25/2010 2.00 AM
5 24 5.doc 01/25/2010 2.01 AM
table 3
BID Status
1 E
2 M
3 P
4 P
5 P
Now I want Query that I want Unique number of records whos status is 'P' that is only Last file whos status is 'P'
Means I want the following output
AID FileName date status
23 3.doc 01/25/2010 1.06 AM P
24 5.doc 01/25/2010 2.01 AM P
Thanks
|
|
|
|
|
Try following query hope it will work for you
select tablesA.AID AID,FileName,tablesA.date date,status
from tablesA
inner join
(
SELECT FileName,BID,AID from tablesB where bid in
(select max(bid) from tablesB group by aid)
) as b on tablesA.AID = b.AID
inner join
tablesC on b.BID = tablesC.BID
|
|
|
|
|
It works....
Great
Thanks for your Help
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
