UPDATE a row from another row in same table

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 a row from another row in same table

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2008-05-07 : 10:13:11

Hello, looking for a help with the following. Here's some sample data:

transid eventid langID name
1 1 1 hello
2 1 2 bonjour
3 2 1 yes
4 2 2 oui
5 3 1 no
6 4 1 cat
7 4 2 chat

I need to write a new record to this table for any record that does not have a langId = 2, copying over all that record's current data. So executing the query on the above data I'd get a new record as in:

transid eventid langID name
8 3 2 no

I do the actual translation of name column later, just looking to get the table updated with the needed records. Transid is an auto counter.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 10:17:52

[code]INSERT INTO NewTable (eventid, langID, name)
SELECT eventid,2,'no'
FROM YourTable
GROUP BY eventid
HAVING SUM(CASE WHEN langID = 2 THEN 1 ELSE 0 END) =0[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 10:26:50

Why restrict to one event or one language?
Do it all at once...


DECLARE	@Sample TABLE (TransID INT, EventID INT, LangID INT, Name VARCHAR(20))

INSERT	@Sample
SELECT	1, 1, 1, 'hello' UNION ALL
SELECT	2, 1, 2, 'bonjour' UNION ALL
SELECT	3, 2, 1, 'yes' UNION ALL
SELECT	4, 2, 2, 'oui' UNION ALL
SELECT	5, 3, 1, 'no' UNION ALL
SELECT	6, 4, 1, 'cat' UNION ALL
SELECT	7, 4, 2, 'chat' UNION ALL
SELECT	8, 6, 2, 'Yak'

SELECT		e.EventID,
		l.LangID,
		'<no>' AS Name
FROM		(
			SELECT		EventID
			FROM		@Sample
			GROUP BY	EventID
		) AS e
CROSS JOIN	(
			SELECT		LangID
			FROM		@Sample
			GROUP BY	LangID
		) AS l
LEFT JOIN	@Sample AS s ON s.EventID = e.EventID
			AND s.LangID = l.LangID
WHERE		s.EventID IS NULL

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources