Solved

Problem with Sub-query

Posted on 2013-10-25
6
431 Views
Last Modified: 2013-10-25
I have two tables, one is a temp table and the other is a permanent.  The temp table is named TempIDsToDelete.  It has two columns, TransID and OrgID.  The permanent table is named CDMNoDecisionHistory.  The code to create it is as follows:

CREATE TABLE [dbo].[CDMNoDecisionHistory](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [CDMTransID] [bigint] NOT NULL,
      [OrgID] [int] NULL,
      [ArchiveUID] [bigint] NULL,
 CONSTRAINT [PK_CDMNoDecisionHistory] PRIMARY KEY CLUSTERED
(      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

I'm attempting to delete records from the permanent table, where CDMNoDecisionHistory.CDMTransID = TransIDsToDelete.TransID using the following query:

DELETE FROM CITI_GTS12..CDMNoDecisionHistory WHERE CDMTransID IN
(SELECT ID FROM TransIDsToDelete)

Now here's my question:  I realize there is a mistake in my inner select statement.  I used the column name ID instead of TransID.  When I attempt to run this query using the incorrect column, it does a volume of work and reports no errors, even though the ID column is incorrect.  The records deleted and much greater than they should be.

To make it more confusing, if I replace ID with TransID in my inner query, it tells me I have an incorrect column name.  What do I need to do to correct this problem?
0
Comment
Question by:navajo26354
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 39601364
I see a CDMTransID.  Is that the field you are looking for?
0
 
LVL 8

Expert Comment

by:Barry62
ID: 39601371
Show us your create statement for TransIDsToDelete
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39601378
Hi Navajo, can you link the code for the temp tables please.  From what you said, TransID is the right column, and your code should work just fine provided that (a) you use the transid column in your subquery and (b) TransID is the correct column name and the column with corresponding values.   The fact that it is telling you that your column is incorrect is definitely the problem.  My initial guess isbtypo and or you should alias the table and use alias.column name to identify
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:navajo26354
ID: 39601404
Here is what I do the create the temp table:

SELECT * INTO TransIDsToDelete  FROM Trans WHERE OrgID IN
(
3995,
4155,
4123
)

This gives me two columns, ID and OrgID.  I then rename the ID column to TransID.  Perhaps this is causing the problem?
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39601491
Hmm... no need for a temp table -- just do this:

DELETE FROM H
FROM dbo.CDMNoDecisionHistory H
INNER JOIN TRANS T
     ON T.OrgID = H.TransID      
WHERE T.OrgID IN (
                      3995,
                      4155,
                      4123
                  )

and if you want to see what you're about to delete... just replace the DELETE FROM H with SELECT * FROM H like this

SELECT *
FROM dbo.CDMNoDecisionHistory H
INNER JOIN TRANS T
     ON T.OrgID = H.TransID      
WHERE T.OrgID IN (
                      3995,
                      4155,
                      4123
                  )
0
 
LVL 3

Accepted Solution

by:
coreconcepts earned 500 total points
ID: 39601549
Navajo, again my above comment will work fine, but just to add to your solution.. if you wanted to make an archive table of the entire row(s) you delete from the NoDecisionHistory table... you could do it all in two statements

The first creates a copy of your NoDecisionHistory table called NoDecisionHistory_Archive...

then you insert into it the OUTPUT of your delete statement (same one from my last comment) as follows:


-- this will create an archive table of your exact table
-- but it won't include constraints
SELECT * INTO DBO.CDMNoDecisionHistory_Archive
FROM [dbo].[CDMNoDecisionHistory]
WHERE 1 = 0

-- Now do your delete in the inner query of this
-- insert statement using the output clause
INSERT INTO DBO.CDMNoDecisionHistory_Archive
SELECT *
FROM (
            DELETE FROM H
            OUTPUT deleted.*  
            FROM dbo.CDMNoDecisionHistory H
            INNER JOIN TRANS T
                   ON T.OrgID = H.TransID      
            WHERE T.OrgID IN (
                                            3995,
                                            4155,
                                            4123
                                      )
        ) as D
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now