Update with Subquery without using temp table

[DirectOne]

I am trying to write sp to update a table with the results of itself having trouble getting it to work without using a temp table.

Anybody have some ideas.
The following works I would like to avoid using temp table if possible.

SELECT CASE i.user_def_fld_4 
		WHEN '' THEN convert(int, (o.qty_to_ship/i.cube_qty_per) )
		WHEN i.cube_qty_per  THEN convert(int, (o.qty_to_ship/i.cube_qty_per))
		ELSE  convert(int,(o.qty_to_ship/i.user_def_fld_4) ) 
		END as cc, o.ord_no
		INTO dbo.#cartoncount
 		FROM dbo.oeordlin_sql o INNER JOIN imitmidx_sql i
		ON o.item_no = i.item_no
		WHERE o.qty_to_ship > 0 and ord_type = 'O'
		GROUP BY o.ord_no, CASE i.user_def_fld_4 
		WHEN '' THEN convert( int, (o.qty_to_ship/i.cube_qty_per ))
		WHEN i.cube_qty_per  
		THEN convert(int,(o.qty_to_ship/i.cube_qty_per))
		ELSE  convert(int,(o.qty_to_ship/i.user_def_fld_4))
		END

UPDATE o
SET o.user_def_fld_2 =  c.cc
FROM oeordlin_sql o
INNER JOIN #cartoncount c on o.ord_no = c.ord_no

 

[jbenson001]

Why the adversion to temp tables?

 

[ShankarJ]

Won't this work? Have not tried subqueries in updates though.

UPDATE o
SET o.user_def_fld_2 = c.cc
FROM oeordlin_sql o
INNER JOIN (SELECT CASE i.user_def_fld_4
WHEN '' THEN convert(int, (oe.qty_to_ship/i.cube_qty_per) )
WHEN i.cube_qty_per THEN convert(int, (oe.qty_to_ship/i.cube_qty_per))
ELSE convert(int,(oe.qty_to_ship/i.user_def_fld_4) )
END as cc, oe.ord_no
FROM dbo.oeordlin_sql oe INNER JOIN imitmidx_sql i
ON oe.item_no = i.item_no
WHERE oe.qty_to_ship > 0 and oe.ord_type = 'O'
GROUP BY oe.ord_no, CASE i.user_def_fld_4
WHEN '' THEN convert( int, (oe.qty_to_ship/i.cube_qty_per ))
WHEN i.cube_qty_per
THEN convert(int,(oe.qty_to_ship/i.cube_qty_per))
ELSE convert(int,(oe.qty_to_ship/i.user_def_fld_4))
END
) c on o.ord_no = c.ord_no

 

[DirectOne]

I always have only used temp tables as a last resort, I am not sure which method is better I have a stored procedure using each method.

Which method preferred?

I have run both in QA with execution plan but do not know how to interpet result.

with temp table

ALTER PROCEDURE don_gotchucks
(@order char(8))

/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	SELECT sum(CASE i.user_def_fld_4 
		WHEN '' THEN convert(int, (o.qty_to_ship/i.cube_qty_per) )
		WHEN i.cube_qty_per  THEN convert(int, (o.qty_to_ship/i.cube_qty_per))
		ELSE  convert(int,(o.qty_to_ship/i.user_def_fld_4) ) 
		END) as cc, o.ord_no
		INTO dbo.#cartoncount
 		FROM dbo.oeordlin_sql o INNER JOIN imitmidx_sql i
		ON o.item_no = i.item_no
		WHERE o.qty_to_ship > 0 and ord_type = 'O' and o.ord_no = @order
		GROUP BY o.ord_no

UPDATE o
SET o.user_def_fld_1 =  c.cc
FROM oeordlin_sql o
INNER JOIN #cartoncount c on o.ord_no = c.ord_no
WHERE o.ord_no = @order

Without temp table

ALTER PROCEDURE don_cartoncount
(@order char(8))
AS
UPDATE o
SET o.user_def_fld_2 = c.cc
	FROM oeordlin_sql o
		INNER JOIN 
		(SELECT sum(CASE i.user_def_fld_4 
		WHEN '' THEN convert(int, (o.qty_to_ship/i.cube_qty_per) )
		WHEN i.cube_qty_per  THEN convert(int, (o.qty_to_ship/i.cube_qty_per))
		ELSE  convert(int,(o.qty_to_ship/i.user_def_fld_4) ) 
		END) as cc, o.ord_no
		FROM dbo.oeordlin_sql o INNER JOIN imitmidx_sql i
		ON o.item_no = i.item_no
		WHERE o.qty_to_ship > 0 and ord_type = 'O' and o.ord_no = @order
		GROUP BY o.ord_no)c
ON o.ord_no = c.ord_no
where o.ord_no = @Order

 

[maswien]

didn't test, but may worth to ry this:

UPDATE o
SET o.user_def_fld_2 = (
CASE i.user_def_fld_4 
        WHEN '' THEN convert(int, (o.qty_to_ship/i.cube_qty_per) )
        WHEN i.cube_qty_per  THEN convert(int, (o.qty_to_ship/i.cube_qty_per))
        ELSE  convert(int,(o.qty_to_ship/i.user_def_fld_4) ) 
        END )
FROM oeordlin_sql o
INNER JOIN imitmidx_sql i
    ON o.item_no = i.item_no
WHERE o.qty_to_ship > 0 and ord_type = 'O'