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 2008 Forums  Transact-SQL (2008)  Update Table Reference with New ID

Author  Topic  CDrag Starting Member 1 Post Posted - 2010-01-20 : 15:09:02 I need to create a query which cross reference another table and updates a link to that table. If the link has no reference then I need to add a new row to the reference table and return the new ID into as a link to the new ID. Here is what I have: Set [Main].[LinkID] = ( (IsNull( Select [RefTable].[RefID] From [RefTable] Where [Main].[Reference] = [RefTable].[Reference]) , 0 ) This works fine to update the reference, but what would I relpace the "0" to reference a new row? I thought I could use the following: ( Insert into [RefTable](Reference]) Values ([Main].[Reference] Return @@Identity ) It doesn't work or won't reference the MAIN table. Let me know if there is any easy way to update this table. Thank you.