updating and delete temp tables 1

[mmetze]

i'm looking at a stored procedure that uses temp tables in an UPDATE...SET...FROM...WHERE stmt- i've never seen this combination before. i will note that in the example below, a column was created (AREA_1) with a very similar name as the temp table (#AREA_1)


UPDATE #TempCurCred
SET chCredentials1_1 = AREA_1
FROM AREA_1
WHERE #TempCurCred = #AREA1.ssn


is this valid? if so, i have another...

DELETE FROM #TempCurCred
FROM #AREA_1
WHERE #TempCurCred.ssn = #AREA_1.ssn
AND #TempCurCred.vcAreaCode = #AREA_1.AREA_1

if valid, what do these stmt do?

 

[skuhlman]

I do something similar except I use a "join" instead of a "where" statement and it works fine. The one thing that looks odd is that the table in your "where" clause does not have a field name.

UPDATE #TempCurCred
SET #TempCurCred.chCredentials1_1 = #AREA1.AREA_1
FROM #AREA_1 JOIN #TempCurCred
ON #TempCurCred.ssn = #AREA1.ssn

the delete should work also

DELETE #TempCurCred
FROM #AREA_1 JOIN #TempCurCred
ON #TempCurCred.ssn = #AREA1.ssn
AND #TempCurCred.vcAreaCode = #AREA_1.AREA_1

 

[mmetze]

in my 1st update stmt- yes, i did leave off the column name in the where clause.

in the delete stmts, are rows being deleted from only the #TempCurCred table? this concept is confusing relating to the normal stmt: 'DELETE FROM table WHERE condition'

 

[SQLBill]

What are you trying to delete? The whole table or just rows?

To delete the whole table, use the DROP TABLE command.

The DELETE command deletes whole rows of data. So you don't specify what you want deleted in the DELETE line. The psuedocode would be:

DELETE (everything) FROM mytable WHERE this occurs

So, I believe your delete statement should look like:

DELETE FROM #TempCurCred
WHERE #TempCurCred.ssn = #AREA_1.ssn
AND #TempCurCred.vcAreaCode = #AREA_1.AREA_1

That will delete ALL rows from #TempCurCred that meet the criteria in the WHERE clause.

-SQLBill

 

[mmetze]

it appears that it is valid to include 2 FROM clauses in a single DELETE stmt. the 2nd is used soley to allow a JOIN to be performed and only rows from the 1st are removed.

special thanks to 'cutiger93' for the following tip...


Create table #TempCurCred(ssn int,vcAreaCode int)


Create Table #AREA_1(AREA_1 int,ssn int)

insert #area_1 select 29673,249638355
insert #TempCurCred select 249638355,29673
insert #TempCurCred select 249638355,29673
insert #area_1 select 29673,249638355
insert #TempCurCred select 249638355,29673
insert #TempCurCred select 249638355,29673
insert #area_1 select 29673,249638355



select * from #area_1
select * from #TempCurCred

DELETE FROM #TempCurCred
FROM #AREA_1
WHERE #TempCurCred.ssn = #AREA_1.ssn
AND #TempCurCred.vcAreaCode = #AREA_1.AREA_1

* only rows from #TempCurCred are removed.

 

[mmetze]

also...

thanks to skuhlman for the tips. the joins work as advertised and take the guesswork out regarding which tables will be affected.