Query problem, help! - 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

Query problem, help!

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-02-08 : 02:17:12

I am i need of a script to update a column in a table based on the result of a select statement below

update table1 set col10 = 'Yes' where (
select
table1.anr,
table1.ardel_id,
min(table1.col_id) as column_id
From
table1 as table1
GROUP BY
table1.anr,
table1.ardel_id)

Please give me an hint how to solve this, as for now I am using a cursor which takes forever to execute.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 02:24:07

What is the criteria to update the table? based on resultset...but how that resultset is related to the UPDATE statement itself?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 02:25:11

you can use inner join.

Can't understand what you are trying to do from the query you posted.

Basically it is something like this


update a
set    col1 = b.cola,
       col2 = 'Yes'
from   table1 a inner join table2 b
on     a.somecol = b.somecol

maevr
Posting Yak Master

169 Posts

Posted - 2007-02-08 : 02:38:24

Thanks for fast reply.

The update should set the col10 to 'Yes' based on other values in the same table, where the select statment is true.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 02:42:41

quote:
where the select statment is true

you mean where the select statement returns records ? like EXISTS ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 02:44:21

[code]
update table1
set col10 = 'Yes'
where exists
(
select
table1.anr,
table1.ardel_id,
min(table1.col_id) as column_id
from
table1 as table1
group by
table1.anr,
table1.ardel_id
)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 02:46:33

How do you correlate the Table1 table with the subquery?

Peter Larsson
Helsingborg, Sweden

maevr
Posting Yak Master

169 Posts

Posted - 2007-02-08 : 03:08:23

Below is the existing cursor which solves the problem at the present, hope this can answer your questions

DECLARE
@anr varchar(10),
@ardel_id varchar(10),
@fastighetsid varchar(10),
@counter integer

DECLARE cursor_huvudfastighet CURSOR FOR
Select
SS1_FASTIGHET.ANR,
SS1_FASTIGHET.ARDEL_ID,
MIN(SS1_FASTIGHET.FASTIGHETS_ID ) AS HUVUDFASTIGHETS_ID
From
SS1_FASTIGHET as SS1_FASTIGHET
GROUP BY
SS1_FASTIGHET.ANR,
SS1_FASTIGHET.ARDEL_ID

set @counter = 0

OPEN cursor_huvudfastighet
FETCH NEXT FROM cursor_huvudfastighet
INTO @anr,@ardel_id,@fastighetsid
WHILE @@FETCH_STATUS = 0
BEGIN
set @counter = @counter + 1
UPDATE SS1_FASTIGHET
set HUVUDFASTIGHET = 'J'
where anr = @anr
and ardel_id = @ardel_id
and fastighets_id = @fastighetsid

print CAST(@counter AS varchar(10))
-- Hämtar nästa variabel
FETCH NEXT FROM cursor_huvudfastighet
INTO @anr,@ardel_id,@fastighetsid
END
CLOSE cursor_huvudfastighet
DEALLOCATE cursor_huvudfastighet

maevr
Posting Yak Master

169 Posts

Posted - 2007-02-12 : 03:35:15

Can't anyone point me in the right direction please :-(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 04:10:38

[code]
UPDATE f
SET HUVUDFASTIGHET = 'J'
FROM SS1_FASTIGHET f
INNER JOIN
(
SELECT SS1_FASTIGHET.ANR,
SS1_FASTIGHET.ARDEL_ID,
MIN(SS1_FASTIGHET.FASTIGHETS_ID) AS HUVUDFASTIGHETS_ID
FROM SS1_FASTIGHET as SS1_FASTIGHET
GROUP BY SS1_FASTIGHET.ANR, SS1_FASTIGHET.ARDEL_ID
) g
ON f.anr = g.ANR
AND f.ardel_id = g.ARDEL_ID
AND f.fastighets_id = g.HUVUDFASTIGHETS_ID
[/code]

maevr
Posting Yak Master

169 Posts

Posted - 2007-02-12 : 08:30:15

It works, Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources