Solved

Help With SQL Statement and Table Variables

Posted on 2011-09-18
3
262 Views
Last Modified: 2012-05-12
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
Comment
Question by:csimmons1324
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 500 total points
ID: 36557768
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
 

Author Comment

by:csimmons1324
ID: 36557778
That seemed to work.  Thank you!  What is the NPTA that you added?
0
 
LVL 2

Expert Comment

by:akku101
ID: 36557782
Given table alias name for New_Product_To_Add  as NPTA.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

737 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