Menu
I have a question about using subqueries in an Update statement. My example:
UPDATE TRIPS
SET locations = city + ', ' FROM (select Distinct city
from poi
where poi.trip_guid = trips.guid)
Is it possible to refer to main table value (trips.guid) in subqueries?
When i try to use trips.guid I get the error:
"The multi-part identifier "trips.guid" could not be bound."
The clause 'select Distinct city from poi' return more that one city.
367
UPDATE Subquery Finally, you can use a subquery in an UPDATE statement for the table to be updated. In the previous examples, we have just used the product table. However, you can use a subquery instead of the product table, which will return a result set that can be updated.
A subquery-also referred to as an inner query or inner select-is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed.
UPDATE statements with a FROM clause are often used to update information in a table based on a table-valued parameter (TVP), or to update columns in a table in an AFTER trigger. For the scenario of update based on a TVP, see Implementing MERGE Functionality in a Natively Compiled Stored Procedure.
Subqueries can be nested in the UPDATE , DELETE , INSERT and SELECT data manipulation (DML) statements.
You can try something like
UPDATE trips
SET locations = t.city + ', ' + poi.city
FROM trips t INNER JOIN
(
select Distinct city, trip_guid from poi
) poi ON t.trip_guid = poi.trip_guid
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
