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

SQL Server 2005 Forums

Transact-SQL (2005)

update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-03 : 11:03:18

Hello everyone,

I need to update Field3 with Field1 value where Field2 can be multiple.
Please take a look Output result.

declare @tbl1 table
(Field1 varchar(50),
Field2 varchar(50),
Field3 varchar (255)
)

INSERT INTO @tbl1
SELECT 'A001', 0,null

INSERT INTO @tbl1
SELECT 'A002',1,null

INSERT INTO @tbl1
SELECT 'A003',1,null

INSERT INTO @tbl1
SELECT 'A004',2,null

INSERT INTO @tbl1
SELECT 'A005',2, null

select * from @tbl1

--Output like this :
Field1 Field2 Field3
A001 0 A001
A002 1 A002
A003 1 A002
A004 2 A004
A005 2 A004

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 11:57:17

[code]

SELECT T1.Field1, T1.Field2, T2.MIN_Field1
FROM @tbl1 AS T1
JOIN
(
SELECT Field2, MIN(Field1) AS MIN_Field1
FROM @tbl1
GROUP BY Field2
) AS T2
ON T2.Field2 = T1.Field2
ORDER BY T1.Field1
[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-03 : 12:25:26

Kristen,

How do i update into Field3??

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-03 : 16:43:14

Guys,

anyone has idea how to update field3? Kristen script just return the data

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-03 : 17:32:58

this?

--SELECT	T1.Field1, T1.Field2, T2.MIN_Field1
UPDATE	T1
SET	Field3 = Field1 -- or do u want T2.MIN_Field1?
FROM	@tbl1 AS T1
	JOIN
	(
		SELECT	Field2, MIN(Field1) AS MIN_Field1
		FROM	@tbl1
		GROUP BY Field2
	) AS T2
ON 	T2.Field2 = T1.Field2

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-01-03 : 19:09:40

Try this ....

;with temp as (select min(Field1) as mi, Field2 from @tbl1 group by Field2)
update @tbl1 set Field3 = t.mi from temp t join @tbl1 t1 on t.field2 = t1.field2

-- check after update
select * from @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-04 : 09:50:40

perfect thanks guys for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources