hi i cant use update like this. how?
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERPROCEDURE [dbo].[tt]
AS
BEGIN
SETNOCOUNTON;
DECLARE @t1TABLE(aint,bint,cint)
DECLARE @t2TABLE(aint,bint,cint)
insertinto @t1select a,b,cfrom table_1where a=1
insertinto @t2select a,b,cfrom table_1
update table_1
set c= @t1.cwhere(@t2.b= @t1.b)
END
ALTER PROCEDURE [dbo].[tt]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t1 TABLE(a int,b int,c int)
DECLARE @t2 TABLE(a int,b int,c int)
insert into @t1
select a,b,c
from table_1
where a=1
insert into @t2
select a,b,c
from table_1
update table_1
set c = @t1.c
where (@t2.b = @t1.b)
END
The problem that is here for sure. You have to reference all tables used in a from or where clause (from to use in the SET clause) You can change this to something like:
update table_1
set c = t1.c
from table_1
join @t1 as t1
on table_1.b = t1.b
join @t2 as t2
on t2.b = t1.b
Not sure of the exact relationships, but this should be something like the syntax you need
You logic is missing some things. What if two values will evaluate from the join ? It would be something like this: UPDATE table_1
Set c = @t1.c
FROM Table_1 t1
CROSS JOIN @t1
INNER JOIN @t2
ON @t1.b = @t2.b
HTH, Jens K. Suessmeyer.
select*from table_1
update table_1
set c=(select(c)from table_1 b
where table_1.b=b.b and a='1')
select*from table_1
this works,. but i need the stored procedure
i could not get the codes you gave me to work, it ran without affecting any rows.