Temp Table - 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

Temp Table

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-04 : 10:21:25

Using a temp table. Have the following so far.

declare @orderline table (
ord_no char (8) not null,
line_seq_no smallint (2) not null,
item_no char (30) not null,
select_cd char (1),
prod_cat char (3),
user_def_fld_1 char (30),
rownum int IDENTITY(1,1) pRIMARY KEY NOT NULL
)

insert into @orderline
select ord_no, line_seq_no, item_no, prod_cat, user_def_fld_1
from oeordlin_sql
where select_cd = 'S'

Here is the sort of logic I want to use next but can't get it straight in my head. For every record I want to set the user_def_fld_1 = rownum but if my product Category is = "UZ" I want to set that records user_def_fld to Rownum-1
update @orderline
set user_def_fld_1 = rownum
if prod_cat = 'UZ' then
set user_def_fld_1 = rownum - 1

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 10:25:26

you mean like:


UPDATE @orderline
SET user_def_fld_1 = CASE prod_cat
	WHEN 'UZ' THEN rownum-1
	ELSE rownum
	END

Is that what you're looking for?

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-04 : 10:37:03

That seemed to work. I think I may have another issue. I had an order with 4 lines. When I ran my script the user_def_fld_1 only updated the first two lines.

Here is what I have. There were many records out there with a Select_cd = 'S' and only the one order was updated.

begin
declare @orderline table (
ord_no char (8) not null,
line_seq_no smallint not null,
item_no char (30) not null,
select_cd char (1),
prod_cat char (3),
user_def_fld_1 char (30),
rownum int IDENTITY(1,1) pRIMARY KEY NOT NULL
)

insert into @orderline
select ord_no, line_seq_no, item_no,select_cd, prod_cat, user_def_fld_1
from oeordlin_sql
where select_cd = 'S'

UPDATE @orderline
SET user_def_fld_1 = CASE prod_cat
WHEN 'UZ' THEN rownum-1
ELSE rownum
END

update oeordlin_sql
set oeordlin_sql.user_def_fld_1 = user_def_fld_1
where oeordlin_sql.ord_no in (select ord_no from @orderline)
end

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-04 : 10:43:34

If I do a
select count(*)
from oeordlin_sql
where select_cd = 'S'

I get 42.

When I run my script I get:

(42 row(s) affected)
(42 row(s) affected)
(43 row(s) affected)

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 10:50:06

ok, well first I'd recommend spelling out your insert, qualifying the columns... you may not have to for this one, but it's much easier to debug.

by "only updated 2 lines" do you mean the user_def_fld_1 only had a value for 2 of 4 rows? In which table?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-04 : 10:50:53

Some more info. Seems to be my last statement. If I do a

select *
from @orderline

after my 2nd statement all the data is correct.

My problem is in here someplace:
Is there a better way to update the original table??

update oeordlin_sql
set oeordlin_sql.user_def_fld_1 = user_def_fld_1
where oeordlin_sql.ord_no in (select ord_no from @orderline)

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 11:01:53

try something like this:


update oeo
set oeo.user_def_fld_1 = o.oeo.user_def_fld_1
from oeordlin_sql oeo
inner join @orderline o
on o.ord_no = oeo.ord_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:06:04

probably you could explain your reqmnt with some sample data in form

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-12-04 : 11:08:19

that seemed to do the trick.

Thanks for all your help.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 11:21:28

Well, you'll probably want to clarify the join operation a bit; as I understand your data, the ord_no column is not unique; you probably want to join on a unique combination of fields. (ord_no and line_seq_no, maybe? I'm guessing here.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources