How do i delete composite rows values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How do i delete composite rows values?

Author

Topic

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-04-11 : 02:27:45

Hi All,

I have a doubt to delete for composite primary key values..
Question:-
---------
Deleting rows in composite(colA and ColB) primary key?

ColA ColB
1 MM
2 AA
3 EE
4 PP

I want to delete the two colums data's..

Thnks in Advice..!

MAG,
Start with the new Idea..
http://mageshkumarm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-11 : 03:01:34

What is the problem ?

Vaibhav T

If I cant go back, I want to go fast...

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-11 : 03:47:53

For your given 4 rows, as first column alone contains unique values so you can use "DELETE FROM yourtablename WHERE ColA IN (1,2,4)"
But deleting rows based on composite key (which is real question)
CREATE TABLE dbo.compositkey
(
ColA int NOT NULL ,
ColB varchar(50) NULL
) ON [PRIMARY]

INSERT INTO dbo.compositkey
SELECT 1, 'MM' UNION ALL
SELECT 2, 'AA' UNION ALL
SELECT 3, 'EE' UNION ALL
SELECT 4, 'PP' UNION ALL
SELECT 5, 'AA' UNION ALL
SELECT 2, 'EE'

DELETE FROM dbo.compositkey
WHERE CAST(ColA AS VARCHAR(2))+ColB IN ('2EE','5AA')

-- But better way is to use following query
DELETE
FROM dbo.compositkey
WHERE ( ColA = 2
AND ColB = 'EE'
)
OR ( ColA = 5
AND ColB = 'AA'
)

SELECT * FROM dbo.compositkey

--------------------------
http://connectsql.blogspot.com/

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-04-11 : 04:27:55

quote:
Originally posted by lionofdezert

For your given 4 rows, as first column alone contains unique values so you can use "DELETE FROM yourtablename WHERE ColA IN (1,2,4)"
But deleting rows based on composite key (which is real question)
CREATE TABLE dbo.compositkey
(
ColA int NOT NULL ,
ColB varchar(50) NULL
) ON [PRIMARY]

INSERT INTO dbo.compositkey
SELECT 1, 'MM' UNION ALL
SELECT 2, 'AA' UNION ALL
SELECT 3, 'EE' UNION ALL
SELECT 4, 'PP' UNION ALL
SELECT 5, 'AA' UNION ALL
SELECT 2, 'EE'

DELETE FROM dbo.compositkey
WHERE CAST(ColA AS VARCHAR(2))+ColB IN ('2EE','5AA')

-- But better way is to use following query
DELETE
FROM dbo.compositkey
WHERE ( ColA = 2
AND ColB = 'EE'
)
OR ( ColA = 5
AND ColB = 'AA'
)

SELECT * FROM dbo.compositkey

--------------------------
http://connectsql.blogspot.com/

Thanks Mr.lionofdezert

MAG,
Start with the new Idea..
http://mageshkumarm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources