SQL question using "update" for multiple rows?

Question

I have experience writing queries in SQL, not much experience using DROP, UPDATE, etc. My question is how to write a statement to update several columns for a hundred+ rows in a table.

I have a table called PARAMETERS with 4 columns:
SKU (Primary key)
Reorder
Max
Min

I want to update the values for Reorder, Max, and Min. The updated values will be based on the carton dimensions which are stored in the table "SKU_DIM" (columns include: SKU, Width, Length, Height)

The columns in table PARAMETERS should be updated by SKU, with the following logic (note that depth & width are pulled from SKU_DIM)

Reorder:
floor(90/least(width, depth))*floor(105/greatest(width, depth)*2/3)

Max:
floor(90/least(width, depth))*floor(105/greatest(width, depth))

Min:
floor(90/least(width, depth))*floor(105/greatest(width, depth)/3)

How could I write an UPDATE statement to change the PARAMTER table based on the above logic, which relies on information from the SKU_DIM table?

Answer 1

Hows it going try this address

http://www.thescripts.com/forum/thread443373.html

seems to have gotten results

Peace out.

Answer 2

You might also try something like: SELECT * FROM my_table WHERE LEFT(name,3) in ('ABC','DEF','XYZ') This is suitable for adhoc queries of smaller tables--this is about simpler typing and not performance. For larger tables or something you intend to run a lot (like inside your app or an SP), General Cucombre's answer will run best. Most systems translate IN statements into the equivalent of a bunch of OR statements anyway. You might also get the advantage of a range seek instead of a table scan. Few systems (if any) will do a seek when a string function is in the WHERE clause; the function processing will slow things down too.

Answer 3

UPDATE p

SET Reorder = FLOOR(90 / CASE WHEN s.width < s.depth THEN s.width ELSE s.depth END) * FLOOR(105 / CASE WHEN s.width > s.depth THEN s.width ELSE s.depth END * 2 / 3),

[Max] = FLOOR(90 / CASE WHEN s.width < s.depth THEN s.width ELSE s.depth END) * FLOOR(105 / CASE WHEN s.width > s.depth THEN s.width ELSE s.depth END),

[Min] = FLOOR(90 / CASE WHEN s.width < s.depth THEN s.width ELSE s.depth END) * FLOOR(105 / CASE WHEN s.width > s.depth THEN s.width ELSE s.depth END / 3)

FROM PARAMETERS p JOIN SKU_DIM s ON p.SKU = s.SKU



Boy, that sure looks messy on here.



You may want to consider making a view instead of using the PARAMETERS table, like so:



CREATE VIEW PARAMETERS2 AS

SELECT

SKU,

FLOOR(90 / CASE WHEN width < depth THEN width ELSE depth END) * FLOOR(105 / CASE WHEN width > depth THEN width ELSE depth END * 2 / 3) AS Reorder,

FLOOR(90 / CASE WHEN width < depth THEN width ELSE depth END) * FLOOR(105 / CASE WHEN width > depth THEN width ELSE depth END) AS [Max],

FLOOR(90 / CASE WHEN width < depth THEN width ELSE depth END) * FLOOR(105 / CASE WHEN width > depth THEN width ELSE depth END / 3) AS [Min]

FROM

SKU_DIM