SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insertion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/10/2013 :  08:24:21  Show Profile  Reply with Quote
Inserted 1 fresh row using my trustworthy SP and got an error by passing a null value

checked to see if the row was there

tried again by ticking the pass null box rather than leaving it
got a return value of 0 instead of nothing

I see there are two rows in there
I want to get rid of one. Even that is impossible. I get an error that two will be erased.
No way to get rid of one
I even tried updating a value in one record, to be able to make them different. Not possible

The errors say I can't even leave the error box unless I choose to erase both

I had to kill SSMS to get it out of my way

I can't let this happen again across a whole database!
I know to erase both rows and re-insert one is probably the only way. I want to save time going round in loops of MS errors again
There isn't IDENTITY in this table, but I'm furious I can't even use the Edit top 200 rows tool for a quick manual fix. DELETE won't work if I specify one row
group by x having count(x) >1 is pointless as a command

All caused by being told nothing was inserted and a new method generated a duplicate

No keys here. How should I make it?

Whoops. Easier than I thought. Rows 59 and 60. How do I specify the number in a statement?

I had to create a new row absolutely manually which sets it apart from the remaining 2
I can select what I want but still can't get rid of the two rows. DELETE from thistable where the column is the same three and the different new value != that
parses and runs successfully but affects 0 rows
They're still there.

Nothing I can do. Can't get rid of one of 'em. Or both of 'em

Edited by - mikebird on 08/10/2013 08:58:54

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 08/10/2013 :  09:40:37  Show Profile  Reply with Quote
Not 100% sure what you are asking for, but try this. Run the query below and see if it selects the two rows in question. It should show the same data, with another column appended to it RN. RN will be distinct for each row. Assuming it is the row with RN=1 that you want to delete, comment out the last SELECT and uncomment the DELETE line and run the code again.
;WITH cte AS
(
	SELECT *,ROW_NUMBER() OVER 
		( PARTITION BY YourConditionThatSelectsTheTwoRows ORDER BY (SELECT NULL)) AS RN
	FROM 
		YourTable
)
SELECT * FROM cte;
--DELETE FROM cte WHERE RN=1;
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  08:54:10  Show Profile  Reply with Quote
This is magic. Thanks.
I'll keep it!

Perfect lesson
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  09:58:54  Show Profile  Reply with Quote

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 11
Invalid object name 'cte'.

This is annoying: still trying to delete two rows where RN is 1 and the other is 2

Trying repeatedly, I get told 1 row affected

I also get 61 rows affected (the total count)

This pretend announcement of count always needs a review. Still nothing's changed. I made a backup first to be sure. I still can't get rid of those two rows. Nothing wrong with your script. I just see the MS SQLsrvr will not play ball. Not even deleting 1 row


Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  10:01:17  Show Profile  Reply with Quote
can I explicitly specify the row number to delete these?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/11/2013 :  10:58:55  Show Profile  Reply with Quote
Can you post your query

Edited by - MuMu88 on 08/11/2013 10:59:09
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  11:27:05  Show Profile  Reply with Quote
;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER
( PARTITION BY name ORDER BY (SELECT name)) AS RN
FROM
[age new]
)
SELECT * FROM cte ; DELETE FROM cte WHERE RN in (1,2) and name = 'Steve Jobs';

I have two rows which are exactly the same. I made a third insertion (MANUALLY!!! - the only way... with my SPadd procedure) with a non-null value to set them apart. James' query made sense. I can see the RN column showing the value 1 and 2 for the dupes. 3 is for the other row. The deletion of either dupe or both shows 0 or 61 rows affected. Nothing happens. I detect there's something afoot with this terrible command interface that any request to remove one of the rows is ignored, with the option window to choose ESC OR to choose YES! remove! Both fail. I think of better design with a PK or IDENTITY column but this is a simple 61-row table. I caused the duplicate row simply because I was given "0 rows affected" and used the same procedure with a slight difference with the same result of 0

I saw the duplicates there. WHY being told 0 when it actually does succeed. This is the root of failure which I need to learn about and can't let this happen again.

I can't even select one row distinctly into a copy table because they won't move.
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  11:31:36  Show Profile  Reply with Quote
Also...

select distinct * from the table shows all the dupes
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/11/2013 :  13:13:04  Show Profile  Reply with Quote
quote:
Originally posted by mikebird

;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER
( PARTITION BY name ORDER BY (SELECT name)) AS RN
FROM
[age new]
)
SELECT * FROM cte ; DELETE FROM cte WHERE RN in (1,2) and name = 'Steve Jobs';

I have two rows which are exactly the same. I made a third insertion (MANUALLY!!! - the only way... with my SPadd procedure) with a non-null value to set them apart. James' query made sense. I can see the RN column showing the value 1 and 2 for the dupes. 3 is for the other row. The deletion of either dupe or both shows 0 or 61 rows affected. Nothing happens. I detect there's something afoot with this terrible command interface that any request to remove one of the rows is ignored, with the option window to choose ESC OR to choose YES! remove! Both fail. I think of better design with a PK or IDENTITY column but this is a simple 61-row table. I caused the duplicate row simply because I was given "0 rows affected" and used the same procedure with a slight difference with the same result of 0

I saw the duplicates there. WHY being told 0 when it actually does succeed. This is the root of failure which I need to learn about and can't let this happen again.

I can't even select one row distinctly into a copy table because they won't move.



Try this:

;WITH cte AS
(
	SELECT *,ROW_NUMBER() OVER 
		( PARTITION BY name ORDER BY (SELECT name)) AS RN
	FROM 
		[age new] 
)
DELETE FROM cte WHERE RN in (1,2) and name = 'Steve Jobs';


Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 08/11/2013 :  16:14:04  Show Profile  Reply with Quote

;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER
( PARTITION BY name ORDER BY (SELECT name)) AS RN
FROM
[age new]
)
DELETE FROM cte WHERE RN in (1,2) and name = 'Steve Jobs';

--this killed all three rows. all I wanted was to leave any one of the three
-- so I did this

insert into [age new]
select top 1 * from dbo.[agebackup] where name = 'Steve Jobs'
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/11/2013 :  21:08:07  Show Profile  Reply with Quote
I am coming late to this party, but Mike, the suggestions that people posted are all meant to do two things.

First, it adds another column to your result set. The combination of name column and this new column RN will be unique. That is to say, there CANNOT be more than one row in the result set that has the same value of name and RN.

Second, now that you have a way of identifying each row uniquely, you can pick and choose which rows to delete.

That means that if you followed MuMu88's suggestion, it should not and could not have deleted 3 rows. So there is something else at play here. You didn't happen run that query more than once, did you?

If you are all set and good to go after inserting the data from your [agebackup] table, please ignore me and go on with your life.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000