Skip to content
Advertisement

SQL Query to update a column based on the values of other columns in the same table

Ok this is difficult to phrase, so here goes…

I am using MS SQL Server 2008 R2. I have a temp table that lets say has two already populated columns. There is a third empty column I want to populate based on the value of the first two columns. What I want to do is create a guid (using NEWUID()) for each matching combo of col1 and col2. Here is a visual example:

Lets say I have a temp table that looks like this initially:

I want it to get updated with new GUIDs so that it looks like this:

Notice how a new GUID is created for each matching pair. So the James/Running combo has the same GUID for all James/Running combos… and the John/Running also has the same GUID for the John/Running combos, but not the same GUID as the James/Running combos do.

I tried to make that as clear as possible, but hopefully that isn’t clear as mud!

Can someone show me what the SQL query would look like in order to update that temp table with the correct GUIDs?

Thanks in advance.

Ryan

Advertisement

Answer

Using NEWID() seems to be a pain. Using it a CTE creates a sperate ID, so you need some intermediary table instead.

Produces

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement