Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 1st, 2005, 04:47 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default Updating first found record

Is there a way to update only the first record you find of an SQL string?

I have a table with records, and some are have the same data, so when I update it, they are all updated. And I need only t o update the first one which has the critics in the SQL String. Can that be done?

I thought of doing a SELECT and the inside a (FOR i=1 to 2) Updating the record.
Still I havenīt managed it..... ANybody can push me in the right way?

Thx,

David


Reply With Quote
  #2 (permalink)  
Old December 1st, 2005, 05:00 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

Which db are you using?

Cheers,

Chris

Reply With Quote
  #3 (permalink)  
Old December 1st, 2005, 05:15 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

SQL Server

thx,
David


Reply With Quote
  #4 (permalink)  
Old December 1st, 2005, 05:33 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hey David,

If you have some sort of field unique in your table, you could do something like...
Code:
UPDATE myTable SET myField = 'myValue' WHERE myUniqueField IN(SELECT TOP 1 myUniqueField FROM myTable [criteria here]);
HTH,

Chris

Reply With Quote
  #5 (permalink)  
Old December 1st, 2005, 07:06 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I will try to explain in details what I have and what I want to acomplish:

I have a table where we have orders, but any order can exists of many sublines(products).
What I want to create is a table which contains the suborders, BUT when one of the fields (place) is duplicated it should only show ONCE that record. This because we have the products in differents places. So for example:

Ordernumber Cliente product place
12345 100 222 333
12345 100 222 333
12345 100 222 444
123456 100 222 555

should become:

Ordernumber Cliente product place
12345 100 222 333
12345 100 222 444
123456 100 222 555

I tried many things, like INSERT INTO newtable SELECT DISTINCT ordernumber,place FROM OLDTABLE WHERE [condicion]

But I didnīt succeed in reducing the lines.

Any ideas???

Thx already,

David


Reply With Quote
  #6 (permalink)  
Old December 1st, 2005, 11:37 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I think I might have found the answer. I added and extra column to the table called ID which is of the format Autoidentifier.
I use the normal INSERT code I had used before and then added the next Sequence:

delete from T1
from MyTable T1, MyTable T2
where T1.ordernumber = T2.ordernumber AND T1.place = T2.place AND T1.ID > T2.ID
( I got the code from http://www.cryer.co.uk/brian/sql/sql...SimilarRecords , thx to Brian Cryer)

I suppos eit is not the best way, as I use twice and SQL sequence, but atleats it works ;)

Still if you believe there is a better way, I am glad to know it !

Thx,

David


Reply With Quote
  #7 (permalink)  
Old December 1st, 2005, 11:47 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

Is there a reason why you don't just check for a matching entry before running your insert query?

Cheers,

Chris

Reply With Quote
  #8 (permalink)  
Old December 1st, 2005, 11:56 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I havenīt give it a thought really. I guess jsut because I donīt want to touch the original Tables. The tables I create will be used by a third company. So giving them less data or our clients is better....And maybe because I have been out of SQl for 5 years ;) The new table is created from 3 diferent tables. How would you approach the idee of checking before running the insert query then?

And.... if you could recommend me a good SQL book from Wrox (why Wrox? I like how they write their books), tell me.... as I feel myself sometimes very little asking things.... :-))

Reply With Quote
  #9 (permalink)  
Old December 1st, 2005, 01:06 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

If you are populating the table from three others, I think I would probably create a union query based on those three other tables, with this you should be able to populate the table in one query with no duplicates.

Can't really recommend a sql book from Wrox I'm afraid - I have a few db books, but these are either not Wrox or not SQL Server.

HTH,

Chris

Reply With Quote
  #10 (permalink)  
Old December 2nd, 2005, 02:09 AM
Authorized User
 
Join Date: Nov 2005
Location: , , Spain.
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I will give it a shot.... and hopefully succeed in it :-)

And again, thx for all the help and y our time.

Regards,

David


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
updating record stoneman Access 1 July 5th, 2005 09:12 AM
how to find record, if not found add in form Sjackson Access 1 May 27th, 2005 10:58 AM
Record not found Display Dept name mateenmohd Classic ASP Basics 0 May 15th, 2005 12:26 AM
Record not updating Tangerine ASP.NET 1.x and 2.0 Application Design 3 March 24th, 2004 11:00 AM
No record found eapsokha Classic ASP Databases 1 February 27th, 2004 01:58 AM



All times are GMT -4. The time now is 09:23 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Đ 2013 John Wiley & Sons, Inc.