update query using where - 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)

update query using where

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-17 : 13:48:40

Table1 looks like:

ID UserName UserNameReferences
1 JoeBlow
2 John
3 Dork JoeBlow
4 Shawn JoeBlow JonVanCanMan

I want this to become:

ID ParentID UserName UserNameReferences
1 1 JoeBlow
2 2 John
3 1 Dork JoeBlow
4 1 Shawn JoeBlow asdfasdf

Basically instead of doing a string lookup on UserNameReferences, I want to update the database and use ParentID.

is this clear?

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-17 : 16:35:46

Ok let me clear my question up!

Right now I'm doing lookups on string data, I want to move that to Integer lookups.

Right now I have 2 guids, Guid and ParentGuid. I do lookups for on ParentGuid.

Table

ID Guid ParentGuid

I added a new column, ParentID

ID ParentID Guid ParentGuid

I need a update query to set the ParentID field.

ideas?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-03-17 : 17:52:40

Were you looking for something like this?

UPDATE table1
SET ParentID = CASE WHEN table1.usernamereferences IS NULL THEN table1.ID ELSE C.parentid END
FROM
(SELECT B.id parentid
FROM table1 A INNER JOIN table1 B
ON A.usernamereferences LIKE '%' + B.username + '%') AS C

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-17 : 18:02:55

Heres my table

1 NULL c95a6a6a-934f-4c64-b950-a7fab9ac73e1 c95a6a6a-934f-4c64-b950-a7fab9ac73e1
2 NULL f3689366-2abb-4e84-b148-73c512a36d74 c95a6a6a-934f-4c64-b950-a7fab9ac73e1
3 NULL 64e14173-ac66-4471-b684-4763e1e16a41 f3689366-2abb-4e84-b148-73c512a36d74
4 NULL db03b1b0-291e-4fd7-9b64-66dec89d3939 f3689366-2abb-4e84-b148-73c512a36d74
5 NULL 9188d709-9d4b-4413-b2fe-7d28dce22f24 db03b1b0-291e-4fd7-9b64-66dec89d3939
6 NULL 35533bbd-0184-4c93-9a9e-10711e312981 9188d709-9d4b-4413-b2fe-7d28dce22f24
7 NULL abfbbcdb-6a36-46cf-8359-b33663dc7b49 9188d709-9d4b-4413-b2fe-7d28dce22f24
8 NULL b5f49b9a-7df8-403b-9ccf-cee5dd21e3f9 db03b1b0-291e-4fd7-9b64-66dec89d3939
9 NULL 6e7e8ebd-8f4c-478b-99b9-70639a9987e3 b5f49b9a-7df8-403b-9ccf-cee5dd21e3f9
10 NULL f4e49f42-c5a4-489e-a34d-45b79c623b01 b5f49b9a-7df8-403b-9ccf-cee5dd21e3f9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-17 : 18:04:03

If you posted DDL for your table, plus INSERT INTO statements for your sample data, the expected result set using that sample data, then an explanation as to how you come up with the parentID values, then we'll be able to help you. Right now, it isn't very clear what you want.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-17 : 20:43:53

I assume UserName is Unique.

SELECT
	ID
	,(SELECT t2.ID FROM Table1 t2 WHERE t2.UserName = t1.UserNameReferences) AS ParentID
	,UserName
	,UserNameReferences
FROM
	Table1 t1

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources