Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2005, 05:47 AM
Authorized User
 
Join Date: Nov 2005
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


 
Old December 1st, 2005, 06:00 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

Which db are you using?

Cheers,

Chris

 
Old December 1st, 2005, 06:15 AM
Authorized User
 
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi Chris,

SQL Server

thx,
David


 
Old December 1st, 2005, 06:33 AM
Friend of Wrox
 
Join Date: Jul 2003
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

 
Old December 1st, 2005, 08:06 AM
Authorized User
 
Join Date: Nov 2005
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


 
Old December 1st, 2005, 12:37 PM
Authorized User
 
Join Date: Nov 2005
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


 
Old December 1st, 2005, 12:47 PM
Friend of Wrox
 
Join Date: Jul 2003
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

 
Old December 1st, 2005, 12:56 PM
Authorized User
 
Join Date: Nov 2005
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.... :-))

 
Old December 1st, 2005, 02:06 PM
Friend of Wrox
 
Join Date: Jul 2003
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

 
Old December 2nd, 2005, 03:09 AM
Authorized User
 
Join Date: Nov 2005
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







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 12:00 PM
No record found eapsokha Classic ASP Databases 1 February 27th, 2004 02:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.