Sql update using subselect

With the help of this syntax, the join condition is defined between the target and source table. In this last line of the query, we chose the manipulation method for the matched rows. Finally, we added the semicolon ; sign because the MERGE statements must end with the semicolon signs. The major characteristic of the subquery is, they can only be executed with the external query. The noticeable difference in this method is, it might be a convenient way to update one column for the tables that have a small number of the rows.

Now we will execute the following query and then will analyze it. After the execution of the update from a select statement the output of the table will be as below;. As we can see, the PersonCityName column data of the Persons table have been updated with the City column data of the AddressList table for the matched records for the PersonId column.

Regarding this method, we should underline the following significant points. If the subquery finds more than one matched row, the update query will return an error, as shown below:. In this article, we learned to update the data in a table with the data where they are contained in other tables. PersonId INT.

PersonName , PersonLastName. N 'Salvador' , N 'Williams' ,. N 'Lawrence' , N 'Brown' ,. N 'Gilbert' , N 'Jones' ,. N 'Ernest' , N 'Smith' ,. N 'Jorge' , N 'Johnson'.

PersonId , PostCode , City. City ,. AddressList Addr. ON Per. ON Addr. PostCode ,. City ;. PostCode ;. SET Persons. Author Recent Posts. Esat Erkec. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec.

Latest posts by Esat Erkec see all. City , Per. PostCode , Per. Is it possible to join multiple tables here? I have a second table that similarly needs to be updated in this fashion but requires a second join.

Yes, you can add any number of joins. But you can only update a single table. In your second query, the join only succeeds if the user already has u. I would not expect the query to do much? I'd suggest asking a new question, perhaps with a SQL Fiddle example.

What would the process be to find the "correct" row in graduation class? Show 5 more comments. At least it does not in SQL Server. EJoshuaS - Reinstate Monica Could you add an explanation of your solution? Please, provide an explanation about your answer, stackoverflow. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.

Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked Related