Select query not working as expected

Transact-SQL
1

hi

i want to select data from a table where the account code adn fundocde are equal to each other and where there in comp a and comp t in the class load code.

here is my sql
select * from dbo.SSCUKWorkingGLDataloadFile2
where AccountCode = AccountCode
and FundCode = FundCode
AND ClassLoadCode = 'Comp A' AND ClassLoadCode = 'Comp T'

i no there is account numbers which has both classloadcode so want to ull them back

2

If I understand correctly, the following should work:

SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2
WHERE AccountCode = FundCode
AND ClassLoadCode = 'Comp A' 
AND ClassLoadCode = 'Comp T'

Then again, I don't know how the ClassLoadCode conditions will work. Is it possible you're looking for?:

SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2
WHERE AccountCode = FundCode
AND (ClassLoadCode = 'Comp A' 
  OR ClassLoadCode = 'Comp T')
3

what im really trying to do is

i have duplicate records for accountcode and i want to delete the record where classload code is = comp T.

4

The duplicates resulted because of your table data. Please check with data and revert if any

5

the duplicates happened as its in the raw data when it hit the tables. so i need to remove them.

6

they have the same accout code and fund code but different balances and class load codes. they just want the one thats has comp a and remove the comp t

7

you can use group by to eliminate the duplicates. but it depends on as per your business requirement

8

does that ave to be done with in a slect or how does the group by work in a dlete

9

To find the AccountCode+FundCode combinations where ClassLoadCode = 'Comp T' as well as a ClassLoadCode = 'Comp A', use this. It lists such rows where ClassLoadCode = 'Comp T'.

 SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2 a
WHERE
	ClassLoadCode = 'Comp T'
	AND EXISTS
	(
		SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2 b
		WHERE 
			a.AccountCode = b.AccountCode
			AND a.FundCode = b.FundCode
			AND ClassLoadCode = 'Comp A'
	)

To delete those rows, replace the select with delete like this:

DELETE FROM dbo.SSCUKWorkingGLDataloadFile2 a
WHERE
	ClassLoadCode = 'Comp T'
	AND EXISTS
	(
		SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2 b
		WHERE 
			a.AccountCode = b.AccountCode
			AND a.FundCode = b.FundCode
			AND ClassLoadCode = 'Comp A'
	)
10

thanks the code does work but it seems to be deleting to much data. its like its removing to many comp t

11

the select pulls back 23 which looks right but when i use the delete is only leaves 23

12

The SELECT and the DELETE should be affecting exactly the same rows. When you run the SELECT, it lets you preview what you are going to delete.

If the select statement is not displaying exactly the rows that you want to delete, change the select query so you get exactly what you want to delete. Then replace the select * with delete, so you will delete exactly those rows.

If you have only 23 rows left after you delete, that probably means that there are only 46 rows in the table? How may rows were in the table before you deleted?

Also, is it possible that you have more than one row for a given combination of AccountCode, FundCode, and ClassLoadCode?

13

there is 390 rows.

the select pulls back 23 rows and that looks to be correct and they are the rows i want to delete.

i use the delete and im left with 23 rows

14

See this example. You can copy and paste this to an SSMS query window and run it to see what it does:

CREATE TABLE #tmp(AccountCode INT, FundCode INT, ClassLoadCode VARCHAR(32));
INSERT INTO #tmp VALUES
(1,100,'Comp T'),(1,100,'Comp A'),
(2,200,'Comp T'),
(3,300,'Comp A');

SELECT * FROM #tmp a
WHERE
	ClassLoadCode = 'Comp T'
	AND EXISTS
	(
		SELECT * FROM dbo.#tmp b
		WHERE 
			a.AccountCode = b.AccountCode
			AND a.FundCode = b.FundCode
			AND ClassLoadCode = 'Comp A'
	)
	
DELETE a FROM #tmp a
WHERE
	ClassLoadCode = 'Comp T'
	AND EXISTS
	(
		SELECT * FROM d*emphasized text*bo.#tmp b
		WHERE 
			a.AccountCode = b.AccountCode
			AND a.FundCode = b.FundCode
			AND ClassLoadCode = 'Comp A'
	)

I had an error in the DELETE statement. It should have been DELETE a FROM..." rather than "DELETE FROM"

DELETE a FROM dbo.SSCUKWorkingGLDataloadFile2 a
WHERE
	ClassLoadCode = 'Comp T'
	AND EXISTS
	(
		SELECT * FROM dbo.SSCUKWorkingGLDataloadFile2 b
		WHERE 
			a.AccountCode = b.AccountCode
			AND a.FundCode = b.FundCode
			AND ClassLoadCode = 'Comp A'
	)
15

That worked. thanks very much for the help