how to update only the first row using update query in sql server 2000
Question
Thursday, March 11, 2010 4:54 PM
i have a table name customer having the following data
cust_name Working_Country
x y
x z
x a
b c
now the sql server 2000 update query : **update customer set wroking_factory ='p' where cust_name='x' **will update the first 3 rows. but i want that only first row is updated. can any one help me how to do this. As LIMIT clause doesnt exist in SQL Server 2000 and also Top clause not working with update query. please reply me to my id engr_saleemwazir@yahoo.com. i need it quickly. thanks
All replies (17)
Thursday, March 11, 2010 4:58 PM ✅Answered | 1 vote
Hi
Basicall you can update a row and give the uniquely identified columns in where clause.
If a row is uniquely identified by cust_name and working_country then you can update the first row in your list as follows:
**update customer set wroking_factory ='p' where cust_name='x' and working_country = 'y'
-Sreekar**
Wednesday, March 17, 2010 4:55 PM ✅Answered | 1 vote
Post result of @@Version and of
SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Wednesday, March 17, 2010 7:16 PM ✅Answered
I don't remember if SQL 2000 had service packs, but I assume it did. Make sure to install the latest SPs on the server.
Also, my question was for the person insisting on ROW_NUMBER() working in SQL 2000. I'm absolutely certain that ROW_NUMBER() is a new function introduced in SQL Server 2005, so that person needs to post the results of the above.Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Friday, March 19, 2010 10:33 AM ✅Answered
Yes Finally i m damn sure that Row_Number() doesn't work with SQL Server 2000. Thanks for all Your replies. in order to handle such type of situation Basicall you can update a row and give the uniquely identified columns in where clause.
If a row is uniquely identified by cust_name and working_country then you can update the first row in your list as follows:
update customer set wroking_factory ='p' where cust_name='x' and working_country = 'y'
if there is no way to uniquely identify a single row then i think design of the database is not fully meaningful or correct.
Friday, March 12, 2010 4:55 PM
yes but this make my final query very lenghty which i want to avoid. my final query is given below. i am assigning a seat_no in a sepcific bus that satisfy the required condition from the passenger. if you look to the given query below. i have to write the last 6 lines extra coz i am assigning a specific seat in a bus having a unique licence plate no (Bus_LP_no).can you help me how i can remove the last six lines. i just want to assign any bus seat but only single one seat reservation i.e one row update.
update Ticket_Details set Ticket_Status = 'Reserved', Passenger_Id = 'saleem' where Ticket_Details.Ticket_Status = 'Available' and
convert(int,Ticket_Details.Seat_No) =
(select TOP 1 Ticket_Details.Seat_NO from Ticket_Details where Ticket_Details.Starting_Terminal = 'ansan' and Ticket_Details.Destination_Terminal = 'suwon' and Ticket_Details.Bus_Type = 'direct'
and Ticket_Details.Travelling_Date = Convert(datetime, '3/10/2010') and Ticket_Details.Ticket_Status = 'Available' and convert(datetime, convert(char(8), Ticket_Details.Starting_Time, 8)) between convert(datetime,Convert(char(8), '9:00', 8)) and convert(datetime,Convert(char(8), '11:45' ,8)))
and Ticket_Details.Bus_LP_No =
(select TOP 1 Ticket_Details.Bus_LP_No from Ticket_Details where Ticket_Details.Starting_Terminal = 'ansan' and Ticket_Details.Destination_Terminal = 'suwon' and Ticket_Details.Bus_Type = 'direct'
and Ticket_Details.Travelling_Date = Convert(datetime, '3/10/2010') and Ticket_Details.Ticket_Status = 'Available' and convert(datetime,convert(char(8), Ticket_Details.Starting_Time,8)) between convert(datetime,Convert(char(8),'9:00', 8)) and convert(datetime, Convert(char(8), '11:45', 8)))
Tuesday, March 16, 2010 3:57 AM | 1 vote
What is your SQL Server version? In SQL Server 2005 and up you can use TOP (N) clause in UPDATE command, so it may be a good solution for your case. Alternatively you can use row_number() function in SQL Server 2005 and up, so
; with cte as (select *, row_number() over (partition by Cust_Name order by WorkingCountry) as row from myTable)
update cte set WorkingContry = 'p' where Cust_Name = 'x' and row = 1 Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Tuesday, March 16, 2010 9:19 AM
Thanks for your reply, but unfortunately i am sing SQL Server 2000 which only support TOP() Clause for 'SQL Select' Query only. Row_number() is also not supported by this version. Is there are alternate way? i will be thankful. i cannot user SQL Server 20005 now because the whole system is already developed in SQL Server 2000, only i need modification.
Tuesday, March 16, 2010 5:40 PM | 1 vote
Hi Saleem_wazir,
**Row_Number() works in sqlserver2000....
**you have to select and run the below two queris at a time.Then you will get a result.other wise it will show error.
**with cte as (select *, row_number() (partition by Cust_Name order by WorkingCountry) as row from myTable)
update cte set WorkingContry = 'p' where Cust_Name = 'x' and row = 1** PS.Shakeer Hussain, Hyderabad
Tuesday, March 16, 2010 8:02 PM | 1 vote
No, this is incorrect. ROW_NUMBER() was introduced in SQL Server 2005 (and other windowing functions).
In SQL Server 2000 it would be a bit trickier and I can not write this right up from the top of my head, though I may research this problem a bit later - I saw the solutions before using temporary table with identity field.Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Wednesday, March 17, 2010 4:47 PM | 1 vote
Hi Naom,
ROW_NUMBER() works in sqlserver 2000.in my system i have sqlserver2000.I run so may queries.it works and giving output well.PS.Shakeer Hussain Hyderabad
Wednesday, March 17, 2010 5:19 PM | 1 vote
No, this is incorrect. ROW_NUMBER() was introduced in SQL Server 2005 (and other windowing functions).
In SQL Server 2000 it would be a bit trickier and I can not write this right up from the top of my head, though I may research this problem a bit later - I saw the solutions before using temporary table with identity field. Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
I Agree With You Naom.Failure in Life is failure to try...
Wednesday, March 17, 2010 5:27 PM | 1 vote
Try
select ID into #temp from myTable T where ID = (select top 1 ID from myTable T2 where T2.Cust_Name = T.Cust_Name and Cust_Name = 'X' order by WorkingCountry) -- to get only 1 record per customer
update T set WorkingCountry = 'p from myTable T inner join #temp TMP on T.ID = TMP.ID where T.Cust_Name = 'x'
drop table #temp
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
Wednesday, March 17, 2010 6:49 PM
Dear all ,
thanks for your interest... i tried row_number() but it doesn't work. May be i am doing some mistake. Dear shakir hussain could you please send me a query which i just copy and run on my sql server whether it works or not.
with cte as (select *, row_number() (partition by Cust_Name order by WorkingCountry) as row from myTable)
update cte set WorkingContry = 'p' where Cust_Name = 'x' and row = 1
**
**
when i run above type of query, it gives me error "incorrect syntax near AS"
i need a solution which makes my given below, query shorter.
update Ticket_Details set Ticket_Status = 'Reserved', Passenger_Id = 'saleem' where Ticket_Details.Ticket_Status = 'Available' and
convert(int,Ticket_Details.Seat_No) =
(select TOP 1 Ticket_Details.Seat_NO from Ticket_Details where Ticket_Details.Starting_Terminal = 'ansan' and Ticket_Details.Destination_Terminal = 'suwon' and Ticket_Details.Bus_Type = 'direct'
and Ticket_Details.Travelling_Date = Convert(datetime, '3/10/2010') and Ticket_Details.Ticket_Status = 'Available' and convert(datetime, convert(char(8), Ticket_Details.Starting_Time, 8)) between convert(datetime,Convert(char(8), '9:00', 8)) and convert(datetime,Convert(char(8), '11:45' ,8)))
and Ticket_Details.Bus_LP_No =
(select TOP 1 Ticket_Details.Bus_LP_No from Ticket_Details where Ticket_Details.Starting_Terminal = 'ansan' and Ticket_Details.Destination_Terminal = 'suwon' and Ticket_Details.Bus_Type = 'direct'
and Ticket_Details.Travelling_Date = Convert(datetime, '3/10/2010') and Ticket_Details.Ticket_Status = 'Available' and convert(datetime,convert(char(8), Ticket_Details.Starting_Time,8)) between convert(datetime,Convert(char(8),'9:00', 8)) and convert(datetime, Convert(char(8), '11:45', 8)))
the last six lines are extra...
Wednesday, March 17, 2010 7:00 PM
SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Results'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Eiction = Personal Edition
Product Verson = 8.00.194
Product Level = RTM
SERVERPROPERTY('ProductVersion') AS ProductVersion,
Wednesday, March 17, 2010 8:29 PM
ok thanks for all ur replies. bye...
Thursday, March 18, 2010 2:10 AM | 1 vote
Hi wazir
Naom had forget to write OVER() in the sqlQuery
wrong query
with cte as (select *, row_number() (partition by Cust_Name order by WorkingCountry) as row from myTable)
update cte set WorkingContry = 'p' where Cust_Name = 'x' and row = 1
**Write Query
**with cte as (select *, row_number() over(partition by name order by name) as row from dupemp)
update cte set name = 'p' where Name = 'x' and row = 1
I run the above query in my sqlserver2000.
To run above query.Plz select two qeries at a time and Run..
its working fine.PS.Shakeer Hussain Hyderabad
Thursday, March 18, 2010 3:32 AM
Once again, you're not running SQL Server 2000.
Post the result of this query
select @@VersionPremature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst