selective update - 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

General SQL Server Forums

New to SQL Server Programming

selective update

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-03-06 : 15:50:58

I have
table T2
id amount ss
1 14 2
2 15 1
3 16 2
4 17 2

table T3

id amount ss
1 3 1
1 4 2
2 5 1
3 6 2
4 7 2
4 8 1
4 9 1

I run
UPDATE t2, t3 SET t2.amount = t3.amount
WHERE t2.id1=t3.id and t3.ss=1;

T2 becomes

ID1 amount ss
1 3 2
2 5 1
3 16 2
4 9 2

but I want t2 to be

ID1 amount ss
1 3 2
2 5 1
3 16 2
4 8 2
how can I do that?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 16:17:53

Try this

UPDATE		T2
SET		T2.Amount = x.Amount
FROM		T2
INNER JOIN	(
			SELECT	ID,
				MIN(Amount) AS Amount
			FROM	T3
			WHERE	SS = 1
		) AS x ON x.ID = T2.ID

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 16:19:01

Why 8 and not 9? It is because you want the MIN() amount that matches? If so, then you can simply write it that way. instead of joining to T3, join to a SELECT statement that gives you one value per id and returns the MIN() of the amount column.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-06 : 16:21:49

... or, you could just cut and paste peso's code ...

peso -- at least give them a few minutes to come up with the solution on their own !