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

Help With SQL Statement and Table Variables

I have a table in my database that holds products for an online store.  For each record there are the following fields:

- Weight
- Physical Weight 1
- Physical Weight 2
- Physical Weight 3
- Dimensional Weight 1
- Dimensional Weight 2
- Dimensional Weight 3

I need to compare the physical weights to the dimensional weights and select the largest.  I then have to sum the three values to compute the weight.  I have attached the code that I am using to do this but the table variables that I am using are retaining the last records information and populating the weight with that information for all of the records.  

I have little to no experience writing SQL statements so I would appreciate any help.
DECLARE @sw1 int,
		@sw2 int,
		@sw3 int;	
		
UPDATE dbo.New_Products_To_Add
SET @sw1 =
	CASE
		WHEN dc_custom_physical_weight_1 > dc_custom_dimensional_weight_1
			THEN dc_custom_physical_weight_1
		ELSE
			dc_custom_dimensional_weight_1
	END; 

UPDATE dbo.New_Products_To_Add
SET @sw2 = 
	CASE
		WHEN dc_custom_physical_weight_2 > dc_custom_dimensional_weight_2
			THEN dc_custom_physical_weight_2
		ELSE
			dc_custom_dimensional_weight_2
	END;

UPDATE dbo.New_Products_To_Add
SET @sw3 = 
	CASE
		WHEN dc_custom_physical_weight_3 > dc_custom_dimensional_weight_3
			THEN dc_custom_physical_weight_3
		ELSE
			dc_custom_dimensional_weight_3
	END;	
	
UPDATE dbo.New_Products_To_Add
SET weight = @sw1 + @sw2 + @sw3

Open in new window

0
csimmons1324
Asked:
csimmons1324
  • 2
1 Solution
 
akku101Commented:
Update NPTA
set
Weight =
(
CASE
            WHEN dc_custom_physical_weight_1 > dc_custom_dimensional_weight_1
                  THEN dc_custom_physical_weight_1
            ELSE
                  dc_custom_dimensional_weight_1
End

) +
(

CASE
            WHEN dc_custom_physical_weight_2 > dc_custom_dimensional_weight_2
                  THEN dc_custom_physical_weight_2
            ELSE
                  dc_custom_dimensional_weight_2
      END


)+
(

CASE
            WHEN dc_custom_physical_weight_3 > dc_custom_dimensional_weight_3
                  THEN dc_custom_physical_weight_3
            ELSE
                  dc_custom_dimensional_weight_3
      END
)
From
dbo.New_Product_To_Add NPTA
0
 
csimmons1324IT ManagerAuthor Commented:
That seemed to work.  Thank you!  What is the NPTA that you added?
0
 
akku101Commented:
Given table alias name for New_Product_To_Add  as NPTA.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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