UPDATEing based on a SELECT - 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)

UPDATEing based on a SELECT

Author

Topic

sky
Starting Member

10 Posts

Posted - 2002-01-23 : 06:35:25

Hi

I want to update a bunch of records in one table. But I want to update them based on a select statment in the where clause.

eg
UPDATE tProducts Set Discount=1
WHERE tProducts.id=(SELECT tProducts.id FROM [complex join] WHERE tGroup.id = 48)

Anyone know how to do this... otherwise I'll have to do it by looping through the select statement in ASP ... long winded.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-23 : 06:57:33

Hi

Does this article help you ?

http://www.sqlteam.com/item.asp?ItemID=3876

Damian

sky
Starting Member

10 Posts

Posted - 2002-01-23 : 07:29:02

It kind of helps

I was looking at that very article when the email came in

... I've got as far as the following query

UPDATE tProducts3ex
SET discountable = 0
FROM dbo.tProducts3ex INNER JOIN
dbo.tSubGroupProducts ON
dbo.tProducts3ex.Id = dbo.tSubGroupProducts.ProductId INNER JOIN
dbo.tSubGroups ON
dbo.tSubGroupProducts.SubGroupId = dbo.tSubGroups.Id INNER
JOIN
dbo.tGroupSubGroups ON
dbo.tSubGroups.Id = dbo.tGroupSubGroups.SubGroupId INNER JOIN
dbo.tGroups ON
dbo.tGroupSubGroups.GroupId = dbo.tGroups.Id
AND dbo.tGroups.Id = 48

Only its effecting all products not just the ones with a tgroup.id of 48 (when it doesn't time out!)

Why?

I've worked out a way of doing this using a temporary table... but there must be an easier way.

here is the temporary table way...

SELECT dbo.tProducts3ex.Id
INTO #tTemp
FROM dbo.tProducts3ex INNER JOIN
dbo.tSubGroupProducts ON
dbo.tProducts3ex.Id = dbo.tSubGroupProducts.ProductId INNER JOIN
dbo.tSubGroups ON
dbo.tSubGroupProducts.SubGroupId = dbo.tSubGroups.Id INNER
JOIN
dbo.tGroupSubGroups ON
dbo.tSubGroups.Id = dbo.tGroupSubGroups.SubGroupId INNER JOIN
dbo.tGroups ON
dbo.tGroupSubGroups.GroupId = dbo.tGroups.Id AND
dbo.tGroups.Id = 48

UPDATE tProducts3ex
SET discountable = 0
FROM dbo.tProducts3ex INNER JOIN
#tTemp ON
dbo.tProducts3ex.Id = #tTemp.id
DROP TABLE #tTemp

Edited by - sky on 01/23/2002 07:43:20

Nazim
A custom title

1408 Posts

Posted - 2002-01-23 : 08:41:02

UPDATE tProducts3ex
SET discountable = 0
FROM dbo.tProducts3ex INNER JOIN
ON
(
SELECT dbo.tProducts3ex.Id
FROM dbo.tProducts3ex INNER JOIN
dbo.tSubGroupProducts ON
dbo.tProducts3ex.Id = dbo.tSubGroupProducts.ProductId INNER JOIN
dbo.tSubGroups ON
dbo.tSubGroupProducts.SubGroupId = dbo.tSubGroups.Id INNER
JOIN
dbo.tGroupSubGroups ON
dbo.tSubGroups.Id = dbo.tGroupSubGroups.SubGroupId INNER JOIN
dbo.tGroups ON
dbo.tGroupSubGroups.GroupId = dbo.tGroups.Id AND
dbo.tGroups.Id = 48
) t
on dbo.tProducts3ex.Id = t.id

or you can try this too

UPDATE t
SET set t.discountable = 0
from dbo.tProducts3ex.Id t
INNER JOIN
dbo.tSubGroupProducts ON
t.Id = dbo.tSubGroupProducts.ProductId INNER JOIN
dbo.tSubGroups ON
dbo.tSubGroupProducts.SubGroupId = dbo.tSubGroups.Id INNER
JOIN
dbo.tGroupSubGroups ON
dbo.tSubGroups.Id = dbo.tGroupSubGroups.SubGroupId INNER JOIN
dbo.tGroups ON
dbo.tGroupSubGroups.GroupId = dbo.tGroups.Id AND
dbo.tGroups.Id = 48

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources