deleting data - 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

General SQL Server Forums

New to SQL Server Programming

deleting data

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-08-17 : 09:32:14

suppose below row is returning me one record

select emp.empname,emp.empid,emp.location,dept.deptname
from emp,dept
where emp.deptid=dept.deptid
group by
emp.deptid

can i delete above record as
delete from dbo.emp where exists
(
select emp.empname,emp.empid,emp.location,dept.deptname
from emp,dept
where emp.deptid=dept.deptid
group by
emp.deptid
)

or is there any alternate way to delete the record?
my requirement is from a complex query which is returning 10 rows,i have to delete
4 unwanted rows from that

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-08-17 : 09:36:56

e.g see below complex query

delete from dbo.[PRODUCTION_DELAY] where exists
(

select
[PRODUCTION_DELAY].[PROD_DELAY_MES_SYSTE_CHR] "MES System",
[PRODUCTION_DELAY].[PROD_DELAY_MES_PLANT_] "MES Plant",
[PRODUCTION_DELAY].[PROD_DELAY_MES_MACHI_] "MES Machine",
[FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO] "SAP Functional Location Num"

FROM [dbo].[PRODUCTION_DELAY] JOIN [dbo].[FUNCTIONAL_LOC] ON
[PRODUCTION_DELAY].[PROD_DELAY_SAP_FUNCT_] = [FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO]
WHERE [PROD_DELAY_MES_SYSTE_CHR]='PIPE'
AND [PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT] IS NOT NULL
AND CAST(YEAR([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) AS VARCHAR(4))+ ' '+
DATENAME(MM, [PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) + ' ' +
CAST(DAY([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) AS VARCHAR(2)) = '2009 October 6'
AND [FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO] = '1023-F310400'

GROUP BY
[PRODUCTION_DELAY].[PROD_DELAY_MES_SYSTE_CHR],
[PRODUCTION_DELAY].[PROD_DELAY_MES_PLANT_],
[PRODUCTION_DELAY].[PROD_DELAY_MES_MACHI_],
[FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO],
[PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]
HAVING
(IsNull(Sum(ROUND([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_DLYCST], 0)), 0) < 0
OR Sum([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_DLYTOT]) > 86400)

)

can i delete data as above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 10:14:38

didnt understand meaning of group by in delete. can you explain the logic you need to apply for deletion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-08-17 : 10:36:12

my requirement is
i want to delete a record which below query returns

select
[PRODUCTION_DELAY].[PROD_DELAY_MES_SYSTE_CHR] "MES System",
[PRODUCTION_DELAY].[PROD_DELAY_MES_PLANT_] "MES Plant",
[PRODUCTION_DELAY].[PROD_DELAY_MES_MACHI_] "MES Machine",
[FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO] "SAP Functional Location Num"

FROM [dbo].[PRODUCTION_DELAY] JOIN [dbo].[FUNCTIONAL_LOC] ON
[PRODUCTION_DELAY].[PROD_DELAY_SAP_FUNCT_] = [FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO]
WHERE [PROD_DELAY_MES_SYSTE_CHR]='PIPE'
AND [PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT] IS NOT NULL
AND CAST(YEAR([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) AS VARCHAR(4))+ ' '+
DATENAME(MM, [PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) + ' ' +
CAST(DAY([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]) AS VARCHAR(2)) = '2009 October 6'
AND [FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO] = '1023-F310400'

GROUP BY
[PRODUCTION_DELAY].[PROD_DELAY_MES_SYSTE_CHR],
[PRODUCTION_DELAY].[PROD_DELAY_MES_PLANT_],
[PRODUCTION_DELAY].[PROD_DELAY_MES_MACHI_],
[FUNCTIONAL_LOC].[FUNCTIONAL_LOC_SAP_FUNCT_LO],
[PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_PRODDT]
HAVING
(IsNull(Sum(ROUND([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_DLYCST], 0)), 0) < 0
OR Sum([PRODUCTION_DELAY].[PRODUCTION_DELAY_MES_DLYTOT]) > 86400)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 10:58:50

delete from where?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 13:42:22

since you have in select list columns deriving from several tables, you should delete rows from each table separately.

besides that, if you are doing group by and having in delete, be sure to understand what are you trying to delete. because you might not end up deleting one row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 13:47:33

you cant have delete with group by, you need to use query and join it to main table to get records to be deleted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 13:50:42

true :) i just want to see this delete query :D

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-08-18 : 01:22:53

so how can i construct a delete query? for this

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 06:18:07

Personally I would use a JOIN


DELETE D
FROM MyTable AS D
     JOIN
     (
         SELECT Col1, Col2, ...
         FROM ...
     ) AS T
          ON T.Col1 = D.Col1
         AND T.Col2 = D.Col2
         ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-18 : 11:18:35

quote:

Originally posted by Kristen

Personally I would use a JOIN


DELETE D
FROM MyTable AS D
     JOIN
     (
         SELECT Col1, Col2, ...
         FROM ...
     ) AS T
          ON T.Col1 = D.Col1
         AND T.Col2 = D.Col2
         ...

Before that I would like to make it as SELECT to confirm that it returns the data to be deleted

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 11:23:44

Yeah, I would do that - and use BEGIN TRANSACTION for the Delete so I could ROLLBACK if needed, and otherwise COMMIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources