Solved

Complicated SQL Update SP

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 99
SQL Server - Slabs 9 40
sql Total query 2 18
T-SQL:  Carrying a Figure from One Row to the Next 14 36
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now