Updating 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

SQL Server 2000 Forums

Transact-SQL (2000)

Updating temp table

Author

Topic

Karander
Starting Member

34 Posts

Posted - 2006-07-20 : 11:43:56

Hi!
I have a problem and I am thinking what is the fastest method to solve the following:

I have a temp table "LISTS" consisted of 5 attributes.
Let's name them : a1, a2, a3, a4, a5. Their type is not important.

I need to update each record in the column a3, a4 and a5.
Values for updating are ruturned by select passing a1 value.

So it should be sth like this:

First loop:

select a3, a4, a5 from AnotherTable where sth = a1;
update first record in LISTS table putting appropiate values into column a3 a4 a5

end of first loop;

Is it possible to do it without cursors??

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 12:14:16

What do you mean by first record in LISTS
What do you do in the next itteration of the loop?
Why First loop? Are there more loops?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 12:31:13

Something like

UPDATE	MyTable
SET	a3 = CASE WHEN a1 = 1 THEN 99 ELSE a3 END,
	a4 = CASE WHEN a1 = 2 THEN -99 ELSE a4 END,
	a5 = CASE WHEN a1 = 3 THEN 12345 ELSE a5 END

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-20 : 12:33:04

Something like this:


update	MyTable
set
	a3 = a.a3,
	a4 = a.a4,
	a5 = a.a5
from
	MyTable
	join
	AnotherTable a
	on MyTable.a1 = a.a1

CODO ERGO SUM

Karander
Starting Member

34 Posts

Posted - 2006-07-20 : 13:49:17

Thank you for answers, but this is not what i wanted ...

I have to update each record in table LISTS, getting values from another select.

to get values to update table LISTS i have to 'select' another table for these values passing to its 'where' value from LISTS' a1 attribute.

For instance first loop is:
1. I get value 'a1' from the first record in table LISTS

2. I use this 'a1' to get values b1, b2, b3 from TABLE2
Sth like select b1, b2, b3 from TABLE2 where field = a1;

3. I update first record in LISTS with:
update LISTS set a3 = b1, a4 = b2, a5 = b3 for first record
end of first loop
end;

I have to do all this operation on each record in table LISTS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 15:55:57

See also the suggestion Michael Valentine Jones posted

UPDATE		L
SET		L.a3 = t2.b1,
		L.a4 = t2.b2,
		L.a5 = t2.b3
FROM		Lists L
INNER JOIN	Table2 t2 ON t2.Field = L.a1

There is no need to use CURSOR for this problem.

Peter Larsson
Helsingborg, Sweden

Karander
Starting Member

34 Posts

Posted - 2006-07-21 : 02:37:04

Hi!
Ok it works, but next problem in this contruction is that
this line:

INNER JOIN Table2 t2 ON t2.Field = L.a1 and t2.field2 = L.a2

will give me several records.
Is it possible to query only first record ordered by desc ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-21 : 05:41:08

Ordered by what desc?
INNER JOIN Table2 t2
ON t2.Field = L.a1
and t2.dte = (select max(dte) from Table2 t3 where t3.Field = t2.Field)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources