Update select where exists

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. Using the classic insert statement, we will be getting an error, observe the query and the action output message. Notice the query and the action output message. On running the above query, we get only a warning message and not an error, but nothing gets changed in the table if we see the results.

Notice the below two images of the action output message and the table data in figure 1. Observe the below query with an action output message received after running it. Also, the action message output said 2 rows affected , which means one got deleted before inserting the other. In all the above examples, we worked on the data which had inserts and updates based on the primary key. What if I would like to check if a field that is not a primary key exists or not and would like to update or insert the table?

There are two options for the same:. StackOverflowUser - It won't matter for this instance. All we want to know is, does a row meet the condition listed? If the schema changes and you mention an explicit column name there, isn't that less future-proof? Please don't ask questions about your answer in another person's answer. Show 7 more comments. What about this? The only problem with this is extra work if the join produces duplicates e. By extra work do u mean the query will take longer to run?

I believe this will give you same result. Devin Burke I didn't do the down voting, but I think whoever did it maybe did it because your original formatting was bad? We posted almost the same thing at the same time so I don't know why you got the down vote. Try with this also. I think this is new for you? Pankaj Pankaj 9, 26 26 gold badges silver badges bronze badges. DanAndrews personally, I prefer to be explicit. What harm is there in putting the alias in the SET line? It's helpful for colleagues reading your code that aren't aware the alias isn't strictly necessary.

If you use sensible aliases it should help document the code better, not make it more cumbersome to read and understand. Aaron Bertrand, You're setting what you're updating, there's nothing to figure out there.

The main reason for the downvote was the missing FROM which is now been modified to include. It's not like it could be updating any other table. It's subjective.