sql question - is there a way to do this?
In SQL is there a way to reference a value of some column in a row you are updating, within an inner select? I'm almost looking for a way to escape the inner select which is difficult because it's the same table so the column has the same name as a column in the outer select. Here's the example:
UPDATE [tableTest]
SET column =
(
SELECT count([ppid])
FROM [tableTest]
WHERE [ppid] IN
(SELECT [ppid] FROM [tableTest] WHERE [pnid] = pnid)
)
... except that the last pnid, the one without the square brackets, really needs to reference the pnid of the row being updated not the row being selected. Thanks!
can anyone else see this, or is it not available because yahoo thinks it might be sql injection?
The mad professor posted code that looks almost exactly like what I'm trying to do, except that both the update and subquery are hitting the same table, and in SQL Server you cannot assign an alias to the table you are updating - so how do I differentiate the update from the subquery when I want to refer to the row in the update within the subquery?