How to Delete First Row In Sql

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Gno TransactionDate Deposit Withdra

1003 07/06/2010 24300.0000 NULL
1003 08/06/2010 NULL 22500.0000
1003 21/08/2010 NULL 618.0000
1003 12/12/2010 10000.0000 NULL
1003 02/02/2011 12500.0000 NULL

i want to delete first row because before Withdra Transaction no need
if the first transaction withdraw is null i dont need only that row
how to delete plz send rply quickly i neeed very urgent

Auctually one gno have many taransactions but we have only consider Between date Transactions.when the withdra amount is >1 after transaction only we have consider so above case we dont need first transaction

Can u give me solution plz try to understand
Thanks

Posted 6-Sep-11 7:46am

JKChowdary

Updated 6-Sep-11 8:06am

v3

Add a Solution

Comments

Bert Mitton 6-Sep-11 13:53pm

I hate to ask this question, but where's the key? To be sure you're deleting the correct line, you need a primary key.

If you have 2 identical transactions, on the same date, you'll end up deleting them both.

JKChowdary 6-Sep-11 14:01pm

one gno have many tanasactions
if the gno have deposit amount before withdrawal i want to only delete the row

JKChowdary 6-Sep-11 14:05pm

Auctually one gno have many taransactions but we have only consider Between date Transactions.when the withdra amount is >1 after transaction only we have consider so above case we dont need first transaction

Can u give me solution plz try to understand
Thanks

JKChowdary 6-Sep-11 13:58pm

one gno have many tanasactions
if the gno have deposit amount before withdrawal i want to only delete the row

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bert Mitton · Answer 1 · 2011-09-06T08:54:00

Mika hit it on the head with the primary key. But just to expand on why you need a primary key...

... you can (and did) create transactions without a key, but to identify a transaction correctly, every time, you need some way to identify it. It's like if someone asked you to go to a parking lot at a mall, and wanted you to find a certain car. Could you reliably get the right car if they told you it was a Honda? They could give you more information, like the year, model, and color, but there could always be multiple cars matching that description. Yet with just 1 piece of information, the license plate number, you could identify the car, because every car has a unique license plate.

The primary key is to the database what a license plate is to a car. You really (really) want to assign primary keys. By defining a column as a primary key, it will prevent duplicates. Now, you can manually assign a primary key, but by using the IDENTITY on the primary key column, the database will automatically assign it. In fact, you won't need to change your INSERT code to do this!

Now how to you get the primary key when you add a row? Say you're adding a row

SQL

INSERT INTO tablename (GNO, TransactionDate, Deposit, Withdrawal) VALUES (1003, '9/6/2011', NULL, 200.00)

You just add the following to the line:

SQL

SELECT SCOPE_IDENTITY() from tablename

So you're complete INSERT command would look like:

SQL

INSERT INTO tablename (GNO, TransactionDate, Deposit, Withdrawal) VALUES (1003, '9/6/2011', NULL, 200.00)  SELECT SCOPE_IDENTITY() from tablename

This will return the primary key to you automatically.

Wendelius · Answer 2 · 2011-09-06T08:07:00

The first problem is that you're missing a primary key. If you would have it (for example a column with IDENTITY[^] definition).

You can delete the row for example by specifying all the columns in the WHERE condition and checking that the deletion affects only 1 row, no more.

But at this point I think the best advice is that you add an identity column into the table and define it as the priomary key. Something like:

ASM

alter table TableName
add TransactionId int identity(1,1) not null

alter table TableName
add constraint pk_Transaction primary key (TransactionId)

after that you can reliably identify each row, no matter what.

And the next thing you can investigate is foreign keys: http://msdn.microsoft.com/en-us/library/aa933117(v=sql.80).aspx[^]

How to Delete First Row In Sql

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0