Solved

Complicated SQL Update SP

Posted on 2011-09-09
5
205 Views
Last Modified: 2012-05-12
Hello;

I have a Complicated select statement that pivots data and creates table columns, thepivot select works a treat - it is --

Select ProductID, [1] AS image1 ,[2] AS image2 ,[3] AS image3 ,[4] AS image4 ,[5] As image5
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = @ProductID
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window


Now I need to do an update on the results returned, is this possible? the code below is my feeble attempt:

Thanks in advance


CREATE PROCEDURE [dbo].[AdminUpdateProductImages]
(
@ProductID int,
@Image2 nvarchar(50),
@Image3 nvarchar(50),
@Image4 nvarchar(50),
@Image5 nvarchar(50),
@Image6 nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
Select ProductID, [1] AS image1 ,[2] AS image2 ,[3] AS image3 ,[4] AS image4 ,[5] As image5
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = @ProductID
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p
	
DECLARE @OldImage1 nvarchar(50)
SELECT @OldImage1 = image1

DECLARE @OldImage2 nvarchar(50)
SELECT @OldImage2 = image2

DECLARE @OldImage3 nvarchar(50)
SELECT @OldImage3 = image3

DECLARE @OldImage4 nvarchar(50)
SELECT @OldImage4 = image4

DECLARE @OldImage5 nvarchar(50)
SELECT @OldImage5 = image5

IF @OldImage1 <> @Image2
Update dbo.[Direct-Product-Image] Set Image = @Image2
where Image = @OldImage1 And ProductID = @ProductID

IF @OldImage2 <> @Image3
Update dbo.[Direct-Product-Image] Set Image = @Image3
where Image = @OldImage2 And ProductID = @ProductID

IF @OldImage3 <> @Image4
Update dbo.[Direct-Product-Image] Set Image = @Image4
where Image = @OldImage3 And ProductID = @ProductID

IF @OldImage4 <> @Image5
Update dbo.[Direct-Product-Image] Set Image = @Image5
where Image = @OldImage4 And ProductID = @ProductID

IF @OldImage5 <> @Image6
Update dbo.[Direct-Product-Image] Set Image = @Image6
where Image = @OldImage5 And ProductID = @ProductID
END

GO

Open in new window

0
Comment
Question by:garethtnash
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36509845
can you explain what you are trying to do?

you don't apear to have an image1 variable etc...
0
 

Author Comment

by:garethtnash
ID: 36509897
Hi LowFatSpread

I'm trying to make @OldImage1 the value Image1 from the results from the previous Select Statement, then once I know the value of that column and the ProductID column, update the column if the value of @Image2 is not the same as @OldImage1

Does that make much sense?

I hope so...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36510016
your input parmeters are @image2 to 6

CREATE PROCEDURE [dbo].[AdminUpdateProductImages]
(
@ProductID int,
@Image2 nvarchar(50),
@Image3 nvarchar(50),
@Image4 nvarchar(50),
@Image5 nvarchar(50),
@Image6 nvarchar(50)
)
AS

Open in new window


the pivot doesnt assign the columns to any variables

Select ProductID, [1] AS image1 ,[2] AS image2 ,[3] AS image3 ,[4] AS image4 ,[5] As image5
FROM
	(
	SELECT ProductID,
		ROW_NUMBER() OVER(PARTITION BY [ProductID] ORDER BY ID ASC) AS ImageNumber,
		Image AS [Image]
	from dbo.[Direct-Product-Image] 
	Where [Default-Image] = 'N' AND ProductID = @ProductID
	) tab
PIVOT 
	(
	MIN([image])
		 for ImageNumber in ([1],[2],[3],[4],[5]) 
	) p

Open in new window


did you mean

Select ProductID, @image1=[1]  ,@image2=[2] ....

Open in new window


why pass the parameters?

it doesn't make sense... tell us what the specification for the procedure requires it to do.
give some example input and the expected results


0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 36510545
First of all, you can't use the output of a select query in further processing unless you put it into a cursor (which I'm not even sure you can do with a pivot), or a temporary table, or if the output is one line, you could assign the output to variables.

Secondly, I'm not sure what your logic is trying to accomplish:

For example, if the first line of output from your pivot is:

ProdID   image1  image2   image3   image4     image5
100       cat.jpg   dog.jpg  cow.jpg duck.jpg    bird.jpg

And your input variables could be:
@image1 = cat.jpg
@image2 = dog.jpg
@image3 = worm.jpg
@image4 = spider.jpg
@image5 = goose.jpg
@image6 = moose.jpg

Image of output from procedure
Please put into words what you want in the end.  My guess is that you have a max of 5 images for each product.  And you want to replace one of the pictures if you've got a new one?

I think it would be better to accomplish this with queries rather than code and without the use of the PIVOT.
0
 

Author Closing Comment

by:garethtnash
ID: 36521915
Thank you
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
backup and restore 21 29
changing page verifacation 1 27
job schedule 8 18
SQL Server Shrink hurting performance? 4 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question