sql delete.... stuck.... - 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 2000 Forums

Transact-SQL (2000)

sql delete.... stuck....

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 07:39:38

Hi All,

Hope someone can help...........
I've got a table here and I need to delete the highest priority records out of this (.e.g. 4 in this case) where the namepart is the same. There will be lots of people in the table (person_id). Hope this is clear!

Many Thanks, Tim


person_id   oldname           namepart casechange starting_position Priority    
----------- -------------------------- ---------- ----------------- ----------- 
3325        NULL              4        TC         0                 1
3325        NULL              4        U          0                 4
3325        Abbott            1        TC         0                 1
3325        Abbott            1        U          0                 4
3325        Abbott            2        TC         0                 1
3325        Abbott            2        U          0                 4
3325        Leanne            5        TC         0                 1
3325        Leanne            5        U          0                 4
3325        Ruth              3        TC         0                 1
3325        Ruth              3        U          0                 4

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 07:40:20

Oh yes, and how do I get the display right on this forum? It keeps doing this!!

Kristen
Test

22859 Posts

Posted - 2004-07-01 : 07:52:07

put [ c o d e ] and [ / c o d e ] around it (without the spaces!) ~~[I expect you can go back and edit it if you want to]~~

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 07:54:38

something like this should work:

Delete FROM table1 t1 INNER JOIN (SELECT PersonId, MAX(Priority) AS Priority FROM table1 GROUP BY PersonId) AS t2 ON t1.PersonId = t2.PersonId AND t1.Priority = t2.Priority

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 09:32:25

Thanks Spirit1,

I think this is what you mean... however it's failing...
Incorrect syntax near the keyword 'Inner'.
Incorrect syntax near the keyword 'As'.

Any ideas?

Thanks, Tim


delete from @allNamesTable  allNT
	Inner join 
	(select person_id, MAX(Priority) as Priority from allNT Group By person_id) as AllNT2 
		on allNT.person_id = AllNT2.person_id 
		and allNT.Priority = AllNT2.Priority

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 09:45:27

what is @allNamesTable? you don't need that... isn't allNT your table name?

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 09:46:27

It's a temporary table that I do need (it's the table from the first select). allNT is just an alias.

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 09:47:46

Sorry to cause confusion! This is the current failing select statement.

[code]
delete from @allNamesTable allNT
inner join
(select person_id, MAX(Priority) as Priority from @allNamesTable Group By person_id) as AllNT2
on allNT.person_id = AllNT2.person_id
and allNT.Priority = AllNT2.Priority
[\code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 09:49:22

aha. you can't use it in that way. you need to build the delete sql string and then execute it.

try this
declare @sql varchar(200)
set @sql = 'delete from ' + @allNamesTable + ' allNT Inner join (select person_id, MAX(Priority) as Priority from ' + @allNamesTable + ' allNT Group By person_id) as AllNT2 on allNT.person_id = AllNT2.person_id and allNT.Priority = AllNT2.Priority'
exec(@sql)

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 10:06:46

This fails with.....
Must declare the variable '@allNamesTable'.

Not sure why.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 10:09:09

wait a minute... your temp table's name is @allNamesTable?? because variables are named wit @ prefix. temp tables are usually named with # prefix.

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 10:10:17

[code]
Declare @allNamesTable Table
(person_id int
, oldname varchar(60)
, newname varchar(60)
, namepart tinyint
, casechange varchar(10)
, starting_position int
, Priority int)
[/code]

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 10:11:18

Is that clear. Perhaps I'm using the wrong naming! I need to keep this table as it is (if possible) as there's a really big script behind this that is optimised!

I greatly appreciate your help!

Thanks, Tim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 10:19:19

tables SHOULD NEVER be named with @ prefix, because this is how sql server recognises variables.
i strongly recomend that you change it's name and scripts.
if you can't chenge it enclose the table name in [] -> [@allNamesTable]:

delete from [@allNamesTable] allNT
inner join
(select person_id, MAX(Priority) as Priority from [@allNamesTable] Group By person_id) as AllNT2
on allNT.person_id = AllNT2.person_id
and allNT.Priority = AllNT2.Priority

i hope this works.

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 10:43:22

Not sure I think it's correct.

----------------------------------------------------------------

A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

Syntax

Note Use DECLARE @local_variable to declare variables of type table.

table_type_definition ::=
TABLE ( { column_definition | table_constraint } [ ,...n ] )

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-01 : 10:44:45

Spirit1....

@Table is a Table VARIABLE, not a temporary table. New thing in SQL2000 - probably what's got you confused (?)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-01 : 10:48:27

damn. that's right... i totally forgot that.... thanx jason
i thought he was using select ... from table into @tempTable

tim:
did [@allNamesTable] work?

Go with the flow & have fun! Else fight the flow :)

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 11:00:59

No it didn't work. Same errors.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-01 : 11:01:09

Also, to use INNER JOIN in that way, you have to do it like this...


DELETE FROM @allNamesTable
FROM @allNamesTable aNT1
INNER JOIN (SELECT person_id,namepart,MAX(Priority) as maxprior FROM @allNamesTable GROUP BY person_id,namepart) AS aNT2
ON aNT1.person_id=aNT2.person_id and aNT2.maxprior=aNT1.priority

2 FROM statements - fun isn't it ?

The first 1 dictates which table you're deleting from, the 2nd sets up the conditional join.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-01 : 11:09:22

quote:
Originally posted by Tim_Field

Sorry to cause confusion! This is the current failing select statement.

[code]
delete from @allNamesTable allNT
inner join
(select person_id, MAX(Priority) as Priority from @allNamesTable Group By person_id) as AllNT2
on allNT.person_id = AllNT2.person_id
and allNT.Priority = AllNT2.Priority
[\code]

It should work as:

[code]
delete allNT from @allNamesTable allNT
inner join
(select person_id, MAX(Priority) as Priority from @allNamesTable Group By person_id) as AllNT2
on allNT.person_id = AllNT2.person_id
and allNT.Priority = AllNT2.Priority
[\code]

Corey

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-07-01 : 11:14:16

Ah ha... that's the one....

Thanks Jason.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources