update all rows in table with data come from temporary table

Question

Post reply

update all rows in table with data come from temporary table

christophe.bernard-680841

Say Hey Kid

Points: 700
More actions
April 29, 2009 at 12:18 am

#155625

Hi everybody,
I m a beginner with sql server 2005 and i have a problem with an update on a table with a table temporary !
here is it my problem !
i have a temporary table as this : it's a stored procedure
DECLARE @tempTableStatutDP TABLE (
dpId int,
dpStatut int
)
INSERT @tempTableStatutDP(dpId, dpStatut)
SELECT
distinct Id
, statut
FROM table1
And with this result i try to update ALL ROWS in table with data on temporary table locate ON THE SAME STORED PROCEDURE:
UPDATE table1
SET statutSpecifique =
(SELECT dpStatut FROM @tempTableStatutDp)
where id = (select dpId FROM @tempTableStatutDp)
but when i execute the stored procedure i have this error :
Msg 512, Level 16, State 1, Procedure up_UPDATE_STATUTSPECIFIQUE, Line 61
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How i can update ALL rows with all rows locate in my temporary table
dpId and id as the same identifient !
Thanks for your time
Christ

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

You should use a Join instead of the subquery for the id.

Try this:

UPDATE TABLE1

SET statutSpecifique =

(SELECT dpStatut FROM @tempTableStatutDp)

FROM Table1 JOIN @tempTableStatutDp t

ON Table1.id = t.dpid

[font="Verdana"]Markus Bohse[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

UPDATE TABLE1

SET statutSpecifique = dpStatut

FROM Table1 JOIN @tempTableStatutDp t

ON Table1.id = t.dpid

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Update A

Set A.statutSpecifique=B.dpStatut

From table1 A

Inner Join @tempTableStatutDp B On A.id=B.dpId

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 4

hi all,

thanks for your time and your solution that s works fine !

i'm happy !

UPDATE table1
SET statutSpecifique = dpStatut
FROM table1, @tempTableStatutDp
WHERE descriptionPosteId = dpId

have a nice day with T SQL 🙂

christophe

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Better use a inner join rather than doing the join in the where clause.

"Keep Trying"