Update using joins- Query returning multiple values

Posted by Jeff Rix on January 13, 2012 under T-SQL | Be the First to Comment

If you are updating a tables column by selecting a column from another table there are different syntax you may use. If the select statement’s column contains more than 1 value for the specified join condition some syntax’s will update the value multiple times while another will fail the update statement.

In this example Dennis Rodman has multiple jersey player numbers in the UpdatePlayerData table.

create table PlayerData

(

Player_ID int identity(1,1) primary key,

Player_Name varchar(50) not null,

Player_Number int not null

)

insert into PlayerData values

('Michael Jordan', 23),('Scottie Pippen', 33),('Dennis Rodman', 91)

create table UpdatePlayerData

(

Player_ID int not null,

Player_Number int not null

)

insert into UpdatePlayerData values

(3, 10),(1, 45),(3,73)

The first two statements execute successfully and use the same execution plan. They update Dennis Rodman’s jersey to 10 and then to 73.

--Update 1

update PlayerData

set Player_Number = x.Player_Number

from

(

select Player_ID, Player_Number from UpdatePlayerData

) as x

where x.Player_ID = PlayerData.Player_ID

--Update 2 

update a

set a.Player_Number = b.Player_Number

from PlayerData as a join

UpdatePlayerData as b on a.Player_ID = b.Player_ID

The last value for his jersey is what is now displayed in the table.

This update syntax will fail the query stating that the sub-query returned more than 1 value.

--Update 3 that will fail 

update PlayerData

set Player_Number = (select upd.Player_Number from UpdatePlayerData as upd

					where upd.Player_ID = PlayerData.Player_ID)

where Player_ID in (select distinct Player_ID from UpdatePlayerData)

Even though it fails in this example I would generally advice using this syntax so you can control the logic that decides which value it will set the column to as opposed to just setting it to the last value it processes.

Dynamic Pivot

Posted by Jeff Rix on December 23, 2011 under T-SQL | Be the First to Comment

Create a table with students, classes, and grades for multiple months.

 

if OBJECT_ID('tempdb..#Student_Grade') is not null drop table #Student_Grade;



create table #Student_Grade

(Student_ID int,

Student_Name varchar(30),

Class_ID int,

Class varchar(20),

Grade decimal(4,2),

Month_Name varchar(20),

Assignment_Type Char(1)

)


INSERT INTO #Student_Grade VALUES...

The records will have one column for month in this table. 

Notice there are two math grades for Will Smith in April.

Suppose we would like to take these months and create columns for them.

Instead of hardcoding the months use the stuff and xml path to get all 

available months in the table.

select

 stuff(

(select N','+quotename(Month_Name) as [text()]

from (

select distinct Month_Name from #Student_Grade) as Month_Name

order by Month_Name

for xml path('')),1,1,'')+ N')) as X;'

declare a variable to hold your results and use the average aggregate 

to get  the average of a students grade for a particular month and course.
DECLARE @sql AS NVARCHAR(1000);


SET @sql = N'select *

from (

	select cast(Student_ID as varchar(20)) + ''. '' + Student_Name as Student,

	cast(Class_ID as varchar(20)) + ''-'' + Class as Class,

	Grade, Month_Name

from #Student_Grade

) as Y

pivot(avg(grade) for Month_Name in ('+

 stuff(

(select N','+quotename(Month_Name) as [text()]

from (

select distinct Month_Name from #Student_Grade) as Month_Name

order by Month_Name

for xml path('')),1,1,'')+ N')) as X;';


select @sql as sql_code

This variable now contains the sql code that will pivot for each month

students  received grades.

select *  from (   select cast(Student_ID as varchar(20)) + '. ' + Student_Name as Student,

cast(Class_ID as varchar(20)) + '-' + Class as Class,   Grade, Month_Name  from #Student_Grade  ) as Y

pivot(avg(grade) for Month_Name in ([April],[February],[January],[March],[May])) as X;

Execute the sql code using exec sp_executesql

exec sp_executesql @stmt = @sql;

81.6 is the average of Will Smiths math grade for English in April.