Solved

Complicated SQL Update SP

Posted on 2011-09-09
5
204 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 36
best counters for cpu high usage 3 33
performance query 4 24
average of calculation (TSQL) 4 10
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 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