Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATE, MERGE or table-valued function UDF? Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 4:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:36 PM
Points: 21, Visits: 238
Hi,

I'm confused how to solve this:
Using UPDATE, MERGE, table-valued UDF or what?
I know using a cursor is possible but there are disadvantages...

tblA
colA1   colA2    colA3
=======================
C 15 NULL
D 17 NULL
G 18 NULL
L 19 NULL
R 20 NULL


tblB (the lookup table)
colB1   colB2    col3
=======================
28 15 1
34 17 1
35 18 1
36 18 0
37 18 0
42 19 1
43 20 0
44 20 1


To do:
Find in tblB the lines with col3 = 1 (exists only once for same colB2-value),
Then UPDATE tblA colA3 with value colB1. RI exists on colB2 = colA2



--
candide
________Panta rhei
Post #1465421
Posted Wednesday, June 19, 2013 4:44 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
It is a simple UPDATE --

UPDATE	tblA
SET colA3 = colB1
FROM tblB
WHERE colA2 = colB2
AND colB3 = 1
;

Post #1465426
Posted Thursday, June 20, 2013 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:36 PM
Points: 21, Visits: 238
thx,
you saved my day


--
candide
________Panta rhei
Post #1465661
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse