Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
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?
3 Answers
- Anonymous1 decade agoFavorite Answer
Y!A doesn't consider this an attempt at SQL injection, it just cuts off lines that are long. If you want to show code, it's better to post it here:
and then incorporate a link to the resulting page in your question on Y!A.
- NickLv 41 decade ago
Is there any chance you can simply say what it is you're trying to do? You haven't explained it very well, and your syntax doesn't help explain it much better. It looks like you're trying to update 'column' withe the count of ppid when ppid = ppid which makes no sense. Can you give some sample data and say what you want to achieve?
- TheMadProfessorLv 71 decade ago
If I interpret you correctly, might try a correlated subselect (never tried one on an update, so not sure it'd work):
UPDATE tableTest u
SET someColumn =
(SELECT count(*) FROM tableTest WHERE ppid IN
(SELECT ppid FROM tableTest c WHERE c.pnid = u.pnid))
Note that even if it works, chances are it'll be a resource hog...correlated subqueries tend to eat up a lot of cycles.
