update 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

update help

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-05 : 07:51:22

I have two tables.

tbl_a

id(pk) name value type
1 a 20 null
2 b 30 null

tbl_b

id name value desc
1 a 10 x
1 a 10 y
2 b 30 gf

the bifurcation of tbl_a is in tbl_b
i need to update col (type) of tbl_a

if there is more than one records in tbl_b for a id than type = 'I'
and when there is single record in tbl_bfor a id in tbl_a then type = 'O'

PLS HELP

challenge everything

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-05 : 09:00:51

You can do it like this:


Update Ex
Set type = (Case When name IN (Select name From (Select *, ROW_NUMBER() Over (Partition By Name Order By Name) As rn From Ex1) As a Where rn > 1)
Then 'I' Else 'O' End)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 09:59:25

[code]
UPDATE a
SET a.[type]=CASE WHEN b.Cnt > 1 THEN 'I' ELSE 'O' END
FROM tbl_a a
CROSS APPLY (SELECT COUNT(1) AS Cnt
FROM tbl_b
WHERE id = a.id
)b
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-05 : 20:05:02

[code]

UPDATE A SET [type] = CASE WHEN A.id = B.ID AND B.REC >1 THEN 'I' ELSE 'O'END

FROM #tbl_a A INNER JOIN
(SELECT *,COUNT(1) OVER(PARTITION BY ID ) REC FROM #tbl_B) b ON A.id = B.ID[/code]

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 21:08:30

quote:

Originally posted by vijays3





UPDATE A SET [type] = CASE WHEN A.id = B.ID AND B.REC >1 THEN 'I' ELSE 'O'END 	


FROM #tbl_a A INNER JOIN 
(SELECT *,COUNT(1) OVER(PARTITION BY ID ) REC FROM #tbl_B) b ON A.id = B.ID

Vijay is here to learn something from you guys.

Isnt this a redundant check as join itself is based on this condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-05 : 22:02:28

quote:
Originally posted by visakh16

quote:
Originally posted by vijays3

UPDATE A SET [type] = CASE WHEN A.id = B.ID AND B.REC >1 THEN 'I' ELSE 'O'END

FROM #tbl_a A INNER JOIN
(SELECT *,COUNT(1) OVER(PARTITION BY ID ) REC FROM #tbl_B) b ON A.id = B.ID

Vijay is here to learn something from you guys.

Isnt this a redundant check as join itself is based on this condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

 Thanks for pointing out..

UPDATE A SET [type] = CASE WHEN  B.REC >1 THEN 'I' ELSE 'O'END 	


FROM #tbl_a A INNER JOIN 
(SELECT *,COUNT(1) OVER(PARTITION BY ID ) REC FROM #tbl_B) b ON A.id = B.ID

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources