compare field values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare field values

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-27 : 13:00:04

Hi.I have 15 fields for my images.
some of them are null & some have values.I need to fill null fields from other fields that have values.
I wrote a query like this:

update tbl_product
set product_image1 = product_image2,product_image2=null
where product_image1 is null AND product_image2 is not null

and it works fine.but I need to do the same thing for all of my 15 fields & compare 1,2 then 2,3 again 1,3 then 3,4 then 3,2,...to get rid of the extra fields with nulls.
is there any other way so I can do this?
thanks for yr help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:02:36

do you have sql server 2005?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 13:18:39

so you need to copy the value from previous or next non null field to nearest null field?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:34:06

I have posted a solution for this some time ago, for SQL Server 2005.
Please feel free to search SQLTeam.com

E 12°55'05.63"
N 56°04'39.26"

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-27 : 13:37:04

its sql 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 13:55:15

You will need something like this

CREATE TABLE	#Dummy (rowID INT IDENTITY(1, 1), pkCol INT, ColNum INT, ColValue SYSNAME)

INSERT		#Dummy (pkCol, ColNum, ColValue)
SELECT		pkCol,
		ColNum,
		ColValue
FROM		(
			SELECT pkCol AS pkCol, 1 AS ColNum, ProductImage1 AS ColValue FROM SourceTable WHERE ProductImage1 IS NOT NULL UNION ALL
			SELECT pkCol, 2, ProductImage2 FROM SourceTable WHERE ProductImage2 IS NOT NULL UNION ALL
			SELECT pkCol, 3, ProductImage3 FROM SourceTable WHERE ProductImage3 IS NOT NULL UNION ALL
			SELECT pkCol, 4, ProductImage4 FROM SourceTable WHERE ProductImage4 IS NOT NULL UNION ALL
			SELECT pkCol, 5, ProductImage5 FROM SourceTable WHERE ProductImage5 IS NOT NULL UNION ALL
			SELECT pkCol, 6, ProductImage6 FROM SourceTable WHERE ProductImage6 IS NOT NULL UNION ALL
			SELECT pkCol, 7, ProductImage7 FROM SourceTable WHERE ProductImage7 IS NOT NULL UNION ALL
			SELECT pkCol, 8, ProductImage8 FROM SourceTable WHERE ProductImage8 IS NOT NULL UNION ALL
			SELECT pkCol, 9, ProductImage9 FROM SourceTable WHERE ProductImage9 IS NOT NULL UNION ALL
			SELECT pkCol, 10, ProductImage10 FROM SourceTable WHERE ProductImage10 IS NOT NULL UNION ALL
			SELECT pkCol, 11, ProductImage11 FROM SourceTable WHERE ProductImage11 IS NOT NULL UNION ALL
			SELECT pkCol, 12, ProductImage12 FROM SourceTable WHERE ProductImage12 IS NOT NULL UNION ALL
			SELECT pkCol, 13, ProductImage13 FROM SourceTable WHERE ProductImage13 IS NOT NULL UNION ALL
			SELECT pkCol, 14, ProductImage14 FROM SourceTable WHERE ProductImage14 IS NOT NULL UNION ALL
			SELECT pkCol, 15, ProductImage15 FROM SourceTable WHERE ProductImage15 IS NOT NULL
		) AS d
ORDER BY 	pkCol,
		ColNum

UPDATE		d
SET		d.ColNum = 1 + d.rowID - x.rowID 
FROM		#Dummy AS d
INNER JOIN	(
			SELECT		pkCol,
					MIN(rowID) AS rowID
			FROM		#Dummy
			GROUP BY	pkCol
		) AS x ON x.pkCol = d.pkCol

UPDATE		st
SET		st.ProductImage1 = d.ProductImage1,
		st.ProductImage2 = d.ProductImage2,
		st.ProductImage3 = d.ProductImage3,
		st.ProductImage4 = d.ProductImage4,
		st.ProductImage5 = d.ProductImage5,
		st.ProductImage6 = d.ProductImage6,
		st.ProductImage7 = d.ProductImage7,
		st.ProductImage8 = d.ProductImage8,
		st.ProductImage9 = d.ProductImage9,
		st.ProductImage10 = d.ProductImage10,
		st.ProductImage11 = d.ProductImage11,
		st.ProductImage12 = d.ProductImage12,
		st.ProductImage13 = d.ProductImage13,
		st.ProductImage14 = d.ProductImage14,
		st.ProductImage15 = d.ProductImage15
FROM		SourceTable AS st
INNER JOIN	(
			SELECT		pkCol,
					MAX(CASE WHEN ColNum = 1 THEN ColValue ELSE NULL END) AS ProductImage1,
					MAX(CASE WHEN ColNum = 2 THEN ColValue ELSE NULL END) AS ProductImage2,
					MAX(CASE WHEN ColNum = 3 THEN ColValue ELSE NULL END) AS ProductImage3,
					MAX(CASE WHEN ColNum = 4 THEN ColValue ELSE NULL END) AS ProductImage4,
					MAX(CASE WHEN ColNum = 5 THEN ColValue ELSE NULL END) AS ProductImage5,
					MAX(CASE WHEN ColNum = 6 THEN ColValue ELSE NULL END) AS ProductImage6,
					MAX(CASE WHEN ColNum = 7 THEN ColValue ELSE NULL END) AS ProductImage7,
					MAX(CASE WHEN ColNum = 8 THEN ColValue ELSE NULL END) AS ProductImage8,
					MAX(CASE WHEN ColNum = 9 THEN ColValue ELSE NULL END) AS ProductImage9,
					MAX(CASE WHEN ColNum = 10 THEN ColValue ELSE NULL END) AS ProductImage10,
					MAX(CASE WHEN ColNum = 11 THEN ColValue ELSE NULL END) AS ProductImage11,
					MAX(CASE WHEN ColNum = 12 THEN ColValue ELSE NULL END) AS ProductImage12,
					MAX(CASE WHEN ColNum = 13 THEN ColValue ELSE NULL END) AS ProductImage13,
					MAX(CASE WHEN ColNum = 14 THEN ColValue ELSE NULL END) AS ProductImage14,
					MAX(CASE WHEN ColNum = 15 THEN ColValue ELSE NULL END) AS ProductImage15
			FROM		#Dummy
			GROUP BY	pkCol
		) AS d ON d.pkCol = st.pkCol

DROP TABLE #Dummy

E 12°55'05.63"
N 56°04'39.26"

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-27 : 14:37:47

wowww

I've tried it :

CREATE TABLE #Dummy (rowID INT IDENTITY(1, 1), pkCol INT, ColNum INT, ColValue SYSNAME)

INSERT #Dummy (pkCol, ColNum, ColValue)
SELECT pkCol,
ColNum,
ColValue
FROM (
SELECT product_id, 1, product_image1 FROM tbl_product WHERE product_image1 IS NOT NULL UNION ALL
SELECT product_id, 2, product_image2 FROM tbl_product WHERE product_image2 IS NOT NULL UNION ALL
SELECT product_id, 3, product_image3 FROM tbl_product WHERE product_image3 IS NOT NULL UNION ALL
SELECT product_id, 4, product_image4 FROM tbl_product WHERE product_image4 IS NOT NULL UNION ALL
SELECT product_id, 5, product_image5 FROM tbl_product WHERE product_image5 IS NOT NULL UNION ALL
SELECT product_id, 6, product_image6 FROM tbl_product WHERE product_image6 IS NOT NULL UNION ALL
SELECT product_id, 7, product_image7 FROM tbl_product WHERE product_image7 IS NOT NULL UNION ALL
SELECT product_id, 8, product_image8 FROM tbl_product WHERE product_image8 IS NOT NULL UNION ALL
SELECT product_id, 9, product_image9 FROM tbl_product WHERE product_image9 IS NOT NULL UNION ALL
SELECT product_id, 10, product_image10 FROM tbl_product WHERE product_image10 IS NOT NULL UNION ALL
SELECT product_id, 11, product_image11 FROM tbl_product WHERE product_image11 IS NOT NULL UNION ALL
SELECT product_id, 12, product_image12 FROM tbl_product WHERE product_image12 IS NOT NULL UNION ALL
SELECT product_id, 13, product_image13 FROM tbl_product WHERE product_image13 IS NOT NULL UNION ALL
SELECT product_id, 14, product_image14 FROM tbl_product WHERE product_image14 IS NOT NULL UNION ALL
SELECT product_id, 15, product_image15 FROM tbl_product WHERE product_image15 IS NOT NULL
) AS d
ORDER BY pkCol,
ColNum

UPDATE d
SET d.ColNum = 1 + d.rowID - x.rowID
FROM #Dummy AS d
INNER JOIN (
SELECT pkCol,
MIN(rowID) AS rowID
FROM #Dummy
GROUP BY pkCol
) AS x ON x.pkCol = d.pkCol

UPDATE st
SET st.product_image1 = d.product_image1,
st.product_image2 = d.product_image2,
st.product_image3 = d.product_image3,
st.product_image4 = d.product_image4,
st.product_image5 = d.product_image5,
st.product_image6 = d.product_image6,
st.product_image7 = d.product_image7,
st.product_image8 = d.product_image8,
st.product_image9 = d.product_image9,
st.product_image10 = d.product_image10,
st.product_image11 = d.product_image11,
st.product_image12 = d.product_image12,
st.product_image13 = d.product_image13,
st.product_image14 = d.product_image14,
st.product_image15 = d.product_image15
FROM tbl_product AS st
INNER JOIN (
SELECT MAX(CASE WHEN ColNum = 1 THEN ColValue ELSE NULL END) AS Product_Image1,
MAX(CASE WHEN ColNum = 2 THEN ColValue ELSE NULL END) AS Product_Image2,
MAX(CASE WHEN ColNum = 3 THEN ColValue ELSE NULL END) AS Product_Image3,
MAX(CASE WHEN ColNum = 4 THEN ColValue ELSE NULL END) AS Product_Image4,
MAX(CASE WHEN ColNum = 5 THEN ColValue ELSE NULL END) AS Product_Image5,
MAX(CASE WHEN ColNum = 6 THEN ColValue ELSE NULL END) AS Product_Image6,
MAX(CASE WHEN ColNum = 7 THEN ColValue ELSE NULL END) AS Product_Image7,
MAX(CASE WHEN ColNum = 8 THEN ColValue ELSE NULL END) AS Product_Image8,
MAX(CASE WHEN ColNum = 9 THEN ColValue ELSE NULL END) AS Product_Image9,
MAX(CASE WHEN ColNum = 10 THEN ColValue ELSE NULL END) AS Product_Image10,
MAX(CASE WHEN ColNum = 11 THEN ColValue ELSE NULL END) AS Product_Image11,
MAX(CASE WHEN ColNum = 12 THEN ColValue ELSE NULL END) AS Product_Image12,
MAX(CASE WHEN ColNum = 13 THEN ColValue ELSE NULL END) AS Product_Image13,
MAX(CASE WHEN ColNum = 14 THEN ColValue ELSE NULL END) AS Product_Image14,
MAX(CASE WHEN ColNum = 15 THEN ColValue ELSE NULL END) AS Product_Image15
FROM #Dummy
GROUP BY pkCol
) AS d ON d.pkCol = st.product_id

DROP TABLE #Dummy

and now I've got 2 errors & I have no idea what should I do about them

Server: Msg 207, Level 16, State 3, Line 37
Invalid column name 'pkCol'.
Server: Msg 8155, Level 16, State 1, Line 3
No column was specified for column 2 of 'd'.

I really appreciate yr help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 14:42:19

"pkCol" must be replaced with the name of the primary key column name you use.
I just wrote pkCol since I know not much about your system.

Also see edit for suggesion above.

E 12°55'05.63"
N 56°04'39.26"

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-05-27 : 15:30:19

thats for editing.
If u check u see I've changed the pkCol name to my primary Key name.but still got the error.
now I even change the name of the primary key product_id to pkCol
but again same error :(

CREATE TABLE #Dummy (rowID INT IDENTITY(1, 1), pkCol INT, ColNum INT, ColValue SYSNAME)

INSERT #Dummy (pkCol, ColNum, ColValue)
SELECT pkCol,
ColNum,
ColValue
FROM (
SELECT pkCol, 1 AS ColNum, product_image1 AS ColValue FROM tbl_product WHERE product_image1 IS NOT NULL UNION ALL
SELECT pkCol, 2 AS ColNum, product_image2 AS ColValue FROM tbl_product WHERE product_image2 IS NOT NULL UNION ALL
SELECT pkCol, 3 AS ColNum, product_image3 AS ColValue FROM tbl_product WHERE product_image3 IS NOT NULL UNION ALL
SELECT pkCol, 4 AS ColNum, product_image4 AS ColValue FROM tbl_product WHERE product_image4 IS NOT NULL UNION ALL
SELECT pkCol, 5 AS ColNum, product_image5 AS ColValue FROM tbl_product WHERE product_image5 IS NOT NULL UNION ALL
SELECT pkCol, 6 AS ColNum, product_image6 AS ColValue FROM tbl_product WHERE product_image6 IS NOT NULL UNION ALL
SELECT pkCol, 7 AS ColNum, product_image7 AS ColValue FROM tbl_product WHERE product_image7 IS NOT NULL UNION ALL
SELECT pkCol, 8 AS ColNum, product_image8 AS ColValue FROM tbl_product WHERE product_image8 IS NOT NULL UNION ALL
SELECT pkCol, 9 AS ColNum, product_image9 AS ColValue FROM tbl_product WHERE product_image9 IS NOT NULL UNION ALL
SELECT pkCol, 10 AS ColNum, product_image10 AS ColValue FROM tbl_product WHERE product_image10 IS NOT NULL UNION ALL
SELECT pkCol, 11 AS ColNum, product_image11 AS ColValue FROM tbl_product WHERE product_image11 IS NOT NULL UNION ALL
SELECT pkCol, 12 AS ColNum, product_image12 AS ColValue FROM tbl_product WHERE product_image12 IS NOT NULL UNION ALL
SELECT pkCol, 13 AS ColNum, product_image13 AS ColValue FROM tbl_product WHERE product_image13 IS NOT NULL UNION ALL
SELECT pkCol, 14 AS ColNum, product_image14 AS ColValue FROM tbl_product WHERE product_image14 IS NOT NULL UNION ALL
SELECT pkCol, 15 AS ColNum, product_image15 AS ColValue FROM tbl_product WHERE product_image15 IS NOT NULL
) AS d
ORDER BY pkCol,
ColNum

UPDATE d
SET d.ColNum = 1 + d.rowID - x.rowID
FROM #Dummy AS d
INNER JOIN (
SELECT pkCol,
MIN(rowID) AS rowID
FROM #Dummy
GROUP BY pkCol
) AS x ON x.pkCol = d.pkCol

UPDATE st
SET st.product_image1 = d.product_image1,
st.product_image2 = d.product_image2,
st.product_image3 = d.product_image3,
st.product_image4 = d.product_image4,
st.product_image5 = d.product_image5,
st.product_image6 = d.product_image6,
st.product_image7 = d.product_image7,
st.product_image8 = d.product_image8,
st.product_image9 = d.product_image9,
st.product_image10 = d.product_image10,
st.product_image11 = d.product_image11,
st.product_image12 = d.product_image12,
st.product_image13 = d.product_image13,
st.product_image14 = d.product_image14,
st.product_image15 = d.product_image15
FROM tbl_product AS st
INNER JOIN (
SELECT MAX(CASE WHEN ColNum = 1 THEN ColValue ELSE NULL END) AS Product_Image1,
MAX(CASE WHEN ColNum = 2 THEN ColValue ELSE NULL END) AS Product_Image2,
MAX(CASE WHEN ColNum = 3 THEN ColValue ELSE NULL END) AS Product_Image3,
MAX(CASE WHEN ColNum = 4 THEN ColValue ELSE NULL END) AS Product_Image4,
MAX(CASE WHEN ColNum = 5 THEN ColValue ELSE NULL END) AS Product_Image5,
MAX(CASE WHEN ColNum = 6 THEN ColValue ELSE NULL END) AS Product_Image6,
MAX(CASE WHEN ColNum = 7 THEN ColValue ELSE NULL END) AS Product_Image7,
MAX(CASE WHEN ColNum = 8 THEN ColValue ELSE NULL END) AS Product_Image8,
MAX(CASE WHEN ColNum = 9 THEN ColValue ELSE NULL END) AS Product_Image9,
MAX(CASE WHEN ColNum = 10 THEN ColValue ELSE NULL END) AS Product_Image10,
MAX(CASE WHEN ColNum = 11 THEN ColValue ELSE NULL END) AS Product_Image11,
MAX(CASE WHEN ColNum = 12 THEN ColValue ELSE NULL END) AS Product_Image12,
MAX(CASE WHEN ColNum = 13 THEN ColValue ELSE NULL END) AS Product_Image13,
MAX(CASE WHEN ColNum = 14 THEN ColValue ELSE NULL END) AS Product_Image14,
MAX(CASE WHEN ColNum = 15 THEN ColValue ELSE NULL END) AS Product_Image15
FROM #Dummy
GROUP BY pkCol
) AS d ON d.pkCol = st.pkCol

DROP TABLE #Dummy

Error:
Server: Msg 207, Level 16, State 3, Line 37
Invalid column name 'pkCol'.

sorry :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 15:49:13

Assuming your primary key is INT, replace all text in red (16 places) to the primary key column name you have in your environment.

CREATE TABLE	#Dummy (rowID INT IDENTITY(1, 1), pkCol INT, ColNum INT, ColValue SYSNAME)

INSERT		#Dummy (pkCol, ColNum, ColValue)
SELECT		pkCol,
		ColNum,
		ColValue
FROM		(
			SELECT pkCol AS pkCol, 1 AS ColNum, Product_Image1 AS ColValue FROM tbl_product WHERE Product_Image1 IS NOT NULL UNION ALL
			SELECT pkCol, 2, Product_Image2 FROM tbl_product WHERE Product_Image2 IS NOT NULL UNION ALL
			SELECT pkCol, 3, Product_Image3 FROM tbl_product WHERE Product_Image3 IS NOT NULL UNION ALL
			SELECT pkCol, 4, Product_Image4 FROM tbl_product WHERE Product_Image4 IS NOT NULL UNION ALL
			SELECT pkCol, 5, Product_Image5 FROM tbl_product WHERE Product_Image5 IS NOT NULL UNION ALL
			SELECT pkCol, 6, Product_Image6 FROM tbl_product WHERE Product_Image6 IS NOT NULL UNION ALL
			SELECT pkCol, 7, Product_Image7 FROM tbl_product WHERE Product_Image7 IS NOT NULL UNION ALL
			SELECT pkCol, 8, Product_Image8 FROM tbl_product WHERE Product_Image8 IS NOT NULL UNION ALL
			SELECT pkCol, 9, Product_Image9 FROM tbl_product WHERE Product_Image9 IS NOT NULL UNION ALL
			SELECT pkCol, 10, Product_Image10 FROM tbl_product WHERE Product_Image10 IS NOT NULL UNION ALL
			SELECT pkCol, 11, Product_Image11 FROM tbl_product WHERE Product_Image11 IS NOT NULL UNION ALL
			SELECT pkCol, 12, Product_Image12 FROM tbl_product WHERE Product_Image12 IS NOT NULL UNION ALL
			SELECT pkCol, 13, Product_Image13 FROM tbl_product WHERE Product_Image13 IS NOT NULL UNION ALL
			SELECT pkCol, 14, Product_Image14 FROM tbl_product WHERE Product_Image14 IS NOT NULL UNION ALL
			SELECT pkCol, 15, Product_Image15 FROM tbl_product WHERE Product_Image15 IS NOT NULL
		) AS d
ORDER BY 	pkCol,
		ColNum

UPDATE		d
SET		d.ColNum = 1 + d.rowID - x.rowID 
FROM		#Dummy AS d
INNER JOIN	(
			SELECT		pkCol,
					MIN(rowID) AS rowID
			FROM		#Dummy
			GROUP BY	pkCol
		) AS x ON x.pkCol = d.pkCol

UPDATE		st
SET		st.Product_Image1 = d.ProductImage1,
		st.Product_Image2 = d.ProductImage2,
		st.Product_Image3 = d.ProductImage3,
		st.Product_Image4 = d.ProductImage4,
		st.Product_Image5 = d.ProductImage5,
		st.Product_Image6 = d.ProductImage6,
		st.Product_Image7 = d.ProductImage7,
		st.Product_Image8 = d.ProductImage8,
		st.Product_Image9 = d.ProductImage9,
		st.Product_Image10 = d.ProductImage10,
		st.Product_Image11 = d.ProductImage11,
		st.Product_Image12 = d.ProductImage12,
		st.Product_Image13 = d.ProductImage13,
		st.Product_Image14 = d.ProductImage14,
		st.Product_Image15 = d.ProductImage15
FROM		SourceTable AS st
INNER JOIN	(
			SELECT		pkCol,
					MAX(CASE WHEN ColNum = 1 THEN ColValue ELSE NULL END) AS ProductImage1,
					MAX(CASE WHEN ColNum = 2 THEN ColValue ELSE NULL END) AS ProductImage2,
					MAX(CASE WHEN ColNum = 3 THEN ColValue ELSE NULL END) AS ProductImage3,
					MAX(CASE WHEN ColNum = 4 THEN ColValue ELSE NULL END) AS ProductImage4,
					MAX(CASE WHEN ColNum = 5 THEN ColValue ELSE NULL END) AS ProductImage5,
					MAX(CASE WHEN ColNum = 6 THEN ColValue ELSE NULL END) AS ProductImage6,
					MAX(CASE WHEN ColNum = 7 THEN ColValue ELSE NULL END) AS ProductImage7,
					MAX(CASE WHEN ColNum = 8 THEN ColValue ELSE NULL END) AS ProductImage8,
					MAX(CASE WHEN ColNum = 9 THEN ColValue ELSE NULL END) AS ProductImage9,
					MAX(CASE WHEN ColNum = 10 THEN ColValue ELSE NULL END) AS ProductImage10,
					MAX(CASE WHEN ColNum = 11 THEN ColValue ELSE NULL END) AS ProductImage11,
					MAX(CASE WHEN ColNum = 12 THEN ColValue ELSE NULL END) AS ProductImage12,
					MAX(CASE WHEN ColNum = 13 THEN ColValue ELSE NULL END) AS ProductImage13,
					MAX(CASE WHEN ColNum = 14 THEN ColValue ELSE NULL END) AS ProductImage14,
					MAX(CASE WHEN ColNum = 15 THEN ColValue ELSE NULL END) AS ProductImage15
			FROM		#Dummy
			GROUP BY	pkCol
		) AS d ON d.pkCol = st.pkCol

DROP TABLE #Dummy