 |
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
|
|
|

December 1st, 2005, 05:47 AM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
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
|

December 1st, 2005, 06:00 AM
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Hi David,
Which db are you using?
Cheers,
Chris
|

December 1st, 2005, 06:15 AM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
Hi Chris,
SQL Server
thx,
David
|

December 1st, 2005, 06:33 AM
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

December 1st, 2005, 08:06 AM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
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
|

December 1st, 2005, 12:37 PM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
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
|

December 1st, 2005, 12:47 PM
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Hi David,
Is there a reason why you don't just check for a matching entry before running your insert query?
Cheers,
Chris
|

December 1st, 2005, 12:56 PM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
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.... :-))
|

December 1st, 2005, 02:06 PM
|
Friend of Wrox
|
|
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

December 2nd, 2005, 03:09 AM
|
Authorized User
|
|
Join Date: Nov 2005
Posts: 72
Thanks: 3
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |