Solved

Complicated SQL Update SP

Posted on 2011-09-09
5
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 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