updating column fields based on other columns

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)

updating column fields based on other columns

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-11-11 : 05:37:31

Hi there,

This is probably quite easy for some so I'd like to ask for help.

My db is developed for a repair centre. A call is made to the centre and a contracter is called out to the site and then makes the repair.

There is a table with four relevant columns to record this info. The first is fldID which is the unique pk for the row. the second is CallID. This holds the ID about which call it is. The third is an EventTypeID
Event 1 = Call logged.
Event 2 = Contractor allocation
Event 3 = third is Onsite
Event 4 = repaired

This means there will be four rows in total with the same callID.

The fourth column is ParentID. Only the Onsite event will have a ParentID, the rest will have -1. The parentID is simply the fldID for the Call logged event (1). This ties it back for relevant reasons.

The problem I have is that there was changes made to the db and subsequently, ParentIDs for about 6000 rows have not been updated and instead of having the fldID from the first row as the parentID in the Onsite Event it is also -1.

I need to find a way to update all rows with their relevant ParentIDs. Does anyone know which is the best method to do this. Below is a diagram of the columns and the way it should be.

Correct - the way it should be


fldID	CallID	EventTypeID	ParentID
ID1	 1	 1	         -1  (call logged event)
ID2	 1	 2	         -1
ID3	 1	 3	         ID1
ID4	 1	 4	         -1

Incorrect - the way it is just now.

fldID	CallID	EventTypeID	ParentID
ID1	 1	 1	         -1 (call logged event)
ID2	 1	 2	         -1
ID3	 1	 3	         -1
ID4	 1	 4	         -1

Many thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-11 : 12:18:47

[code]UPDATE t
SET t.ParentID=t1.fldID
FROM table t
INNER JOIN table t1
ON t.CallID=t1.CallID
AND t.EventTypeID=3
AND t1.EventTypeID=1
[/code]

cidr
Posting Yak Master

207 Posts

Posted - 2009-11-12 : 08:14:25

Thanks for getting back to me visakh16 (again)

I worked this out, however, I went with your code as I was using subqueries and it was getting a little convoluted.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources