[2K5]Syntaxe Update Set From Join Group by

**Monstros Velu** · 14/10/2007, 13h10

Bonjour,

je n'arrive pas à écrire une requête, je dois avoir un problème de syntaxe, mais je ne trouve pas lequel. Voici la requête, qui sert à déterminer les mains (HND_HAND) correspondantes à des quintes flush et à leur attribuer un score fonction de la carte la plus haute :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
update HND_HAND
set HND_SCORE = max(v1.VLE_SCORE)
from HND_HAND
	join HND_CRD hc1
		on HND_HAND.HND_ID = hc1.HND_ID
			join CRD_CARD c1
				on hc1.CRD_ID = c1.CRD_ID
				join VLE_VALUE v1
					on v1.FAC_ID = c1.FAC_ID
	join HND_CRD hc2
		on HND_HAND.HND_ID = hc2.HND_ID
			join CRD_CARD c2
				on hc2.CRD_ID = c2.CRD_ID
				and c2.CLR_ID = c1.CLR_ID
				join VLE_VALUE v2
					on v2.FAC_ID = c2.FAC_ID
					and v2.VLE_SCORE = v1.VLE_SCORE - 1
	join HND_CRD hc3
		on HND_HAND.HND_ID = hc3.HND_ID
			join CRD_CARD c3
				on hc3.CRD_ID = c3.CRD_ID
				and c3.CLR_ID = c2.CLR_ID
				join VLE_VALUE v3
					on v3.FAC_ID = c3.FAC_ID
					and v3.VLE_SCORE = v2.VLE_SCORE - 1
	join HND_CRD hc4
		on HND_HAND.HND_ID = hc4.HND_ID
			join CRD_CARD c4
				on hc4.CRD_ID = c4.CRD_ID
				and c4.CLR_ID = c3.CLR_ID
				join VLE_VALUE v4
					on v4.FAC_ID = c4.FAC_ID
					and v4.VLE_SCORE = v3.VLE_SCORE - 1
	join HND_CRD hc5
		on HND_HAND.HND_ID = hc5.HND_ID
			join CRD_CARD c5
				on hc5.CRD_ID = c5.CRD_ID
				and c5.CLR_ID = c4.CLR_ID
				join VLE_VALUE v5
					on v5.FAC_ID = c5.FAC_ID
					and v5.VLE_SCORE = v4.VLE_SCORE - 1
	group by h.HND_ID

et l'erreur :

Msg 156, Niveau 15, État 1, Ligne 47
Syntaxe incorrecte vers le mot clé 'group'.

J'ai bien essayé de déplacer le group by, mais je ne vois pas.

Pour ceux qui voudrait essayer, voilà le script de création de la base, mais l'insertion est assez longue (1h30 pour la dernière partie, sur un C2D E6600 avec 2Go de RAM)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
 
/****** Objet :  Table [dbo].[CLR_COLOR]    Date de génération du script : 10/14/2007 01:10:51 ******/
CREATE TABLE [CLR_COLOR](
	[CLR_ID] [int] NOT NULL,
	[CLR_SYMBOL] [varchar](1) NOT NULL,
	[CLR_LABEL] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CLR_COLOR] PRIMARY KEY CLUSTERED 
(
	[CLR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
/****** Objet :  Table [dbo].[FAC_FACE]    Date de génération du script : 10/14/2007 01:10:53 ******/
CREATE TABLE [FAC_FACE](
	[FAC_ID] [int] NOT NULL,
	[FAC_SYMBOL] [varchar](1) NOT NULL,
	[FAC_LABEL] [varchar](50) NOT NULL,
 CONSTRAINT [PK_FAC_FACE] PRIMARY KEY CLUSTERED 
(
	[FAC_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
/****** Objet :  Table [dbo].[VLE_VALUE]    Date de génération du script : 10/14/2007 01:10:57 ******/
CREATE TABLE [VLE_VALUE](
	[VLE_ID] [int] NOT NULL,
	[VLE_SCORE] [int] NOT NULL,
	[FAC_ID] [int] NOT NULL,
 CONSTRAINT [PK_VLE_VALUE] PRIMARY KEY CLUSTERED 
(
	[VLE_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_FAC_IDinVLE_VALUE] ON [dbo].[VLE_VALUE] 
(
	[FAC_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
/****** Objet :  Table [dbo].[CRD_CARD]    Date de génération du script : 10/14/2007 01:10:52 ******/
CREATE TABLE [CRD_CARD](
	[CRD_ID] [int] NOT NULL,
	[CLR_ID] [int] NOT NULL,
	[FAC_ID] [int] NOT NULL,
 CONSTRAINT [PK_CRD_CARD] PRIMARY KEY CLUSTERED 
(
	[CRD_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
/****** Objet :  Table [dbo].[HND_HAND]    Date de génération du script : 10/14/2007 01:10:55 ******/
CREATE TABLE [HND_HAND](
	[HND_ID] [int] NOT NULL,
	[HND_SCORE] [decimal](18, 6) NULL,
	[HND_RANK] [int] NULL,
 CONSTRAINT [PK_HND_HAND] PRIMARY KEY CLUSTERED 
(
	[HND_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_HND_SCOREinHND_HAND] ON [dbo].[HND_HAND] 
(
	[HND_SCORE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_HND_RANKinHND_HAND] ON [dbo].[HND_HAND] 
(
	[HND_RANK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
/****** Objet :  Table [dbo].[HND_CRD]    Date de génération du script : 10/14/2007 01:10:54 ******/
CREATE TABLE [HND_CRD](
	[HND_ID] [int] NOT NULL,
	[CRD_ID] [int] NOT NULL,
 CONSTRAINT [PK_HND_CRD] PRIMARY KEY CLUSTERED 
(
	[HND_ID] ASC,
	[CRD_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_CRDinHND_CRD] ON [dbo].[HND_CRD] 
(
	[CRD_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_HNDinHND_CRD] ON [dbo].[HND_CRD] 
(
	[HND_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
/****** Objet :  ForeignKey [CRD_CLR]    Date de génération du script : 10/14/2007 01:10:52 ******/
ALTER TABLE [CRD_CARD]  WITH CHECK ADD  CONSTRAINT [CRD_CLR] FOREIGN KEY([CLR_ID])
REFERENCES [CLR_COLOR] ([CLR_ID])
GO
ALTER TABLE [CRD_CARD] CHECK CONSTRAINT [CRD_CLR]
GO
 
/****** Objet :  ForeignKey [CRD_FAC]    Date de génération du script : 10/14/2007 01:10:52 ******/
ALTER TABLE [CRD_CARD]  WITH CHECK ADD  CONSTRAINT [CRD_FAC] FOREIGN KEY([FAC_ID])
REFERENCES [FAC_FACE] ([FAC_ID])
GO
ALTER TABLE [CRD_CARD] CHECK CONSTRAINT [CRD_FAC]
GO
 
/****** Objet :  ForeignKey [FK_CRD_ID]    Date de génération du script : 10/14/2007 01:10:54 ******/
ALTER TABLE [HND_CRD]  WITH CHECK ADD  CONSTRAINT [FK_CRD_ID] FOREIGN KEY([CRD_ID])
REFERENCES [CRD_CARD] ([CRD_ID])
GO
ALTER TABLE [HND_CRD] CHECK CONSTRAINT [FK_CRD_ID]
GO
 
/****** Objet :  ForeignKey [FK_HND_ID]    Date de génération du script : 10/14/2007 01:10:54 ******/
ALTER TABLE [HND_CRD]  WITH CHECK ADD  CONSTRAINT [FK_HND_ID] FOREIGN KEY([HND_ID])
REFERENCES [HND_HAND] ([HND_ID])
GO
ALTER TABLE [HND_CRD] CHECK CONSTRAINT [FK_HND_ID]
GO
 
/****** Objet :  ForeignKey [FAC_VLE]    Date de génération du script : 10/14/2007 01:10:57 ******/
ALTER TABLE [VLE_VALUE]  WITH CHECK ADD  CONSTRAINT [FAC_VLE] FOREIGN KEY([FAC_ID])
REFERENCES [FAC_FACE] ([FAC_ID])
GO
ALTER TABLE [VLE_VALUE] CHECK CONSTRAINT [FAC_VLE]
GO
 
 
insert into CLR_COLOR(CLR_ID, CLR_SYMBOL, CLR_LABEL)
values(1, 't','Trèfle')
insert into CLR_COLOR(CLR_ID, CLR_SYMBOL, CLR_LABEL)
values(2, 'c','Coeur')
insert into CLR_COLOR(CLR_ID, CLR_SYMBOL, CLR_LABEL)
values(3, 'p','Pique')
insert into CLR_COLOR(CLR_ID, CLR_SYMBOL, CLR_LABEL)
values(4, 'k','Carreau')
GO
 
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(1, '1','As')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(2, 'R','Roi')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(3, 'D','Dame')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(4, 'V','Valet')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(5, 'T','10')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(6, '9','9')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(7, '8','8')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(8, '7','7')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(9, '6','6')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(10, '5','5')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(11, '4','4')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(12, '3','3')
insert into FAC_FACE(FAC_ID, FAC_SYMBOL, FAC_LABEL)
values(13, '2','2')
GO
 
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(1, 1,14)
insert inTO VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(2, 2,13)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(3, 3,12)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(4, 4,11)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(5, 5,10)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(6, 6,9)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(7, 7,8)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(8, 8,7)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(9, 9,6)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(10, 10,5)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(11, 11,4)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(12, 12,3)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(13, 13,2)
insert into VLE_VALUE(VLE_ID, FAC_ID, VLE_SCORE)
values(14, 1,1)
GO
 
insert into CRD_CARD(CRD_ID, CLR_ID, FAC_ID)
select (52*(CLR_ID - 1) + FAC_ID), CLR_ID, FAC_ID
from CLR_COLOR, FAC_FACE
 
 
-- !! Tres long !!--
 
CREATE TABLE ##TMP(
	[HND_ID] [int] IDENTITY(1,1) NOT NULL,
	[CRD_ID1] [int] NOT NULL,
	[CRD_ID2] [int] NOT NULL,
	[CRD_ID3] [int] NOT NULL,
	[CRD_ID4] [int] NOT NULL,
	[CRD_ID5] [int] NOT NULL)
 
CREATE NONCLUSTERED INDEX IX_HND_IDinTMP ON ##TMP 
(
	[HND_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
insert into ##TMP(CRD_ID1, CRD_ID2, CRD_ID3, CRD_ID4, CRD_ID5)
select c1.CRD_ID, c2.CRD_ID, c3.CRD_ID, c4.CRD_ID, c5.CRD_ID
from CRD_CARD c1
join CRD_CARD c2
	on c1.CRD_ID < c2.CRD_ID
join CRD_CARD c3
	on c2.CRD_ID < c3.CRD_ID
join CRD_CARD c4
	on c3.CRD_ID < c4.CRD_ID
join CRD_CARD c5
	on c4.CRD_ID < c5.CRD_ID
 
declare @HND_ID int, @CRD_ID1 int, @CRD_ID2 int, @CRD_ID3 int, @CRD_ID4 int, @CRD_ID5 int
 
declare @NBR int
select @NBR = 1 + max(HND_ID) from ##TMP
set @HND_ID = 1
 
while(@HND_ID < @NBR)
begin
	select 		@CRD_ID1 = CRD_ID1, 
				@CRD_ID2 = CRD_ID2, 
				@CRD_ID3 = CRD_ID3, 
				@CRD_ID4 = CRD_ID4, 
				@CRD_ID5 = CRD_ID5
	from ##TMP
	where HND_ID = @HND_ID
 
	insert into HND_HAND(HND_ID)
	select @HND_ID
 
	insert into HND_CRD(HND_ID, CRD_ID)
	select @HND_ID, @CRD_ID1
	insert into HND_CRD(HND_ID, CRD_ID)
	select @HND_ID, @CRD_ID2
	insert into HND_CRD(HND_ID, CRD_ID)
	select @HND_ID, @CRD_ID3
	insert into HND_CRD(HND_ID, CRD_ID)
	select @HND_ID, @CRD_ID4
	insert into HND_CRD(HND_ID, CRD_ID)
	select @HND_ID, @CRD_ID5
 
	set @HND_ID = 1+ @HND_ID
end

Si vous voyez le problème, n'hésitez pas, merci ! Et si vous connaissez de la documentation sur cette syntaxe... Je n'en ai pas trouvé...

**ylarvor** · 14/10/2007, 14h30

Je ne pense pas qu'il soit possible de faire un group by directement sur un update... voila pourquoi tu as une erreur... as tu essayer de faire un select @var = max() puis un update @var ?

**SQLpro** · 15/10/2007, 10h57

Essaye :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
UPDATE HND_HAND
SET HND_SCORE = max(v1.VLE_SCORE)
FROM HND_HAND AS h
	JOIN HND_CRD hc1
		ON h.HND_ID = hc1.HND_ID
			JOIN CRD_CARD c1
				ON hc1.CRD_ID = c1.CRD_ID
				JOIN VLE_VALUE v1
					ON v1.FAC_ID = c1.FAC_ID
	JOIN HND_CRD hc2
		ON h.HND_ID = hc2.HND_ID
			JOIN CRD_CARD c2
				ON hc2.CRD_ID = c2.CRD_ID
				AND c2.CLR_ID = c1.CLR_ID
				JOIN VLE_VALUE v2
					ON v2.FAC_ID = c2.FAC_ID
					AND v2.VLE_SCORE = v1.VLE_SCORE - 1
	JOIN HND_CRD hc3
		ON h.HND_ID = hc3.HND_ID
			JOIN CRD_CARD c3
				ON hc3.CRD_ID = c3.CRD_ID
				AND c3.CLR_ID = c2.CLR_ID
				JOIN VLE_VALUE v3
					ON v3.FAC_ID = c3.FAC_ID
					AND v3.VLE_SCORE = v2.VLE_SCORE - 1
	JOIN HND_CRD hc4
		ON hD.HND_ID = hc4.HND_ID
			JOIN CRD_CARD c4
				ON hc4.CRD_ID = c4.CRD_ID
				AND c4.CLR_ID = c3.CLR_ID
				JOIN VLE_VALUE v4
					ON v4.FAC_ID = c4.FAC_ID
					AND v4.VLE_SCORE = v3.VLE_SCORE - 1
	JOIN HND_CRD hc5
		ON h.HND_ID = hc5.HND_ID
			JOIN CRD_CARD c5
				ON hc5.CRD_ID = c5.CRD_ID
				AND c5.CLR_ID = c4.CLR_ID
				JOIN VLE_VALUE v5
					ON v5.FAC_ID = c5.FAC_ID
					AND v5.VLE_SCORE = v4.VLE_SCORE - 1
	GROUP BY h.HND_ID

A +

[2K5]Syntaxe Update Set From Join Group by

MS SQL Server

Discussions similaires

Partager

Partager