Update using a temp table, Sql Server

Update using a temp table

-- ///////////SCHEMA///////////////////////////////////////////////////
CREATE TABLE Animals
    ([animal] varchar(255), [legs] int, [colour] varchar(255))
;

CREATE TABLE ObjectPermissions
    ([animal] varchar(255), [permission] varchar(255), [record] int, [test1] varchar(255), [test2] varchar(255), [test3] varchar(255) )
;

INSERT INTO Animals
    ([animal], [legs], [colour])
VALUES
    ('cat', 4, 'red'),
    ('bird', 2, 'yellow'),
    ('dog', 4, 'red'),
    ('deer', 4, 'red'),
    ('fish', 0, 'blue'),
    ('dingo', 4, 'yellow')
;

select * from animals
-- ////////////////////////////////////////////////////////////////////

DECLARE @temptable table (
        animal VARCHAR(max),
        legs VARCHAR(max)
)

INSERT INTO @temptable (animal, legs) VALUES ('Fish' , 10)
INSERT INTO @temptable (animal, legs) VALUES ('Dog', 1)

select * into #temp from @temptable

Update Animals
Set legs = 
(Select #temp.legs
from #temp where #temp.animal = Animals.animal)
Where Exists (Select #temp.legs
from #temp where #temp.animal = Animals.animal)
AND Exists (Select #temp.legs
from #temp where #temp.animal = Animals.animal)

drop table #temp;

select * from animals


run \| edit \| history \| help	0

Please log in to post a comment.