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.
| Author |
Topic |
|
Boxersoft
Starting Member
28 Posts |
 Posted - 2010-03-05 : 10:50:58
|
| I want to populate fields in a table with values from a self-referential subquery. At least, I think that's what I mean! Say I have the following data:ID=1, Name=Fred, ParentName=JoeID=2, Name=Joe, ParentName=BillI want to populate a ParentID column. I thought I could do something like:UPDATE person p1 SET ParentID = (SELECT id FROM person p2 WHERE p2.name = p1.ParentName)... but SQL Server complains "Incorrect syntax near 'p1'". I found mention of a FROM part in an UPDATE (SET id = p2.id FROM person p2) but ran into similar problems there, i.e. it didn't like the table alias in the UPDATE part and without it I couldn't figure how to refer back to the row in that part from within the 'subquery' part.I'm sure it must be possible. Any suggestions? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 11:21:25
|
| [code]UPDATE p1 SET p1.ParentID = p2.idFROM person p1INNER JOIN person p2ON p2.name = p1.ParentName[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Boxersoft
Starting Member
28 Posts |
Posted - 2010-03-05 : 11:51:47
|
| Arghhh! I had tried almost exactly that. My mistake was that I included the table name as well as the alias in the first part, i.e.:UPDATE person p1 // FailsUPDATE p1 [...] // Works.Thanks very much! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 12:25:53
|
exactly..either alias or table name not both  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Update with subquery?