Insertion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insertion

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-10 : 08:24:21

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-10 : 09:40:37

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;

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 08:54:10

This is magic. Thanks.
I'll keep it!

Perfect lesson

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 09:58:54

(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

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 10:01:17

can I explicitly specify the row number to delete these?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-11 : 10:58:55

Can you post your query

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 11:27:05

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

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 11:31:36

Also...

select distinct * from the table shows all the dupes

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-11 : 13:13:04

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:
[CODE]
;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';

[/CODE]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-08-11 : 16:14:04

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-08-11 : 21:08:07

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources