3KM5diUV

1Variable Table Update Case
2SELECT a.Id, a.Description, a.Speed, CASE 
3    WHEN EXISTS(SELECT b.Id FROM TableB b WHERE b.Id = a.Id) THEN
4        UPDATE TableB SET OriginalSpeed = 
5            CASE
6                WHEN (a.Speed is not null) THEN a.Speed
7                WHEN a.Description = 'Motorway' THEN 70
8                WHEN a.Description = 'A Road' THEN 60
9                ELSE 30
10            END
11    ELSE
12        UPDATE TableA SET Speed =
13            CASE
14                WHEN (a.Speed is not null) THEN a.Speed
15                WHEN a.Description = 'Motorway' THEN 70
16                WHEN a.Description = 'A Road' THEN 60
17                ELSE 30
18            END
19END
20FROM TableA a
21	
22UPDATE
23    b
24SET
25    OriginalSpeed = CASE
26                WHEN (a.Speed is not null) THEN a.Speed
27                WHEN a.Description = 'Motorway' THEN 70
28                WHEN a.Description = 'A Road' THEN 60
29                ELSE 30
30            END
31FROM
32    TableB b
33        inner join
34    TableA a
35        on
36            b.Id = a.Id
37
38UPDATE
39    a
40SET
41    Speed = CASE
42                WHEN (a.Speed is not null) THEN a.Speed
43                WHEN a.Description = 'Motorway' THEN 70
44                WHEN a.Description = 'A Road' THEN 60
45                ELSE 30
46            END
47FROM
48    TableA a
49        left join
50    TableB b
51        on
52           a.ID = b.ID
53WHERE
54    b.ID is null
55	
56Create Proc dbo.usp_ProcedureName
57
58@Id int,
59@Speed int
60
61As Begin
62
63
64If Exists (Select ID From TableB Where Id = @Id)
65    Begin
66
67        Update TableB
68        Set OriginalSpeed = @Speed
69        Where Id = @Id
70
71
72    End
73Else
74    Begin
75
76        Update TableA
77        Set Speed = @Speed
78        Where Id = @Id
79
80    End
81
82
83
84End
85Go