deleting rows from temp table - 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)

deleting rows from temp table

Author

Topic

jryannel
Starting Member

13 Posts

Posted - 2003-12-29 : 15:30:34

I created 2 temp tables with data and would like to remove data from one temp table based on information from the other temp table, but I'm having problems. Below is my code and the error.

delete from #tempdata2 where #tempdata2.[first name] = #tempdata.first_name

The column prefix '#tempdata' does not match with a table name or alias name used in the query.

Can anyone help, Thanks jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-29 : 15:43:18

[code]delete #tempdata2
from #tempdata2 td2
inner join #tempdata td on td.first_name = td2.[first name]
[/code]

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-12-29 : 15:45:23

How about this -

delete #tempdata2 from #tempdata2 join #tempdata on #tempdata2.[first name] = #tempdata.first_name

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-29 : 15:50:02

[code]
CREATE TABLE #tempdata([first name] varchar(50))
CREATE TABLE #tempdata2(first_name varchar(50))

INSERT INTO #tempdata([first name]) VALUES('John')
INSERT INTO #tempdata([first name]) VALUES('Michael')

INSERT INTO #tempdata2(first_name) VALUES('John')
INSERT INTO #tempdata2(first_name) VALUES('Michael')
INSERT INTO #tempdata2(first_name) VALUES('Yoda')

DELETE FROM #tempdata2
where #tempdata2.first_name IN (SELECT [first name] FROM #tempdata)

--This should leave you with just Yoda
SELECT * from #tempdata2

DROP TABLE #tempdata2
DROP TABLE #tempdata

[/code]

BTW
<Yoda>[First Name] and First_Name, confusing it is.</Yoda>

[EDIT]
<Yoda>Sniped I was, yess.....</Yoda>
[/EDIT]
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jryannel
Starting Member

13 Posts

Posted - 2003-12-29 : 16:21:53

thanks, michael that was perfect!

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-29 : 16:37:15

I...I Can't * believe it Jedi Yak

[Jedi Yak]
That its why you fail...
[/Jedi Yak]

(* Or was it don't)

The Yak is strong with this one.....(what's that smell?)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources