• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Complicated SQL Update SP

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
garethtnash
Asked:
garethtnash
  • 2
  • 2
1 Solution
 
LowfatspreadCommented:
can you explain what you are trying to do?

you don't apear to have an image1 variable etc...
0
 
garethtnashAuthor Commented:
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
 
LowfatspreadCommented:
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
 
dwe761Software EngineerCommented:
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
 
garethtnashAuthor Commented:
Thank you
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now