Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 UPDATE  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2867
Вроде бы везде один и тот же стиль дан, не могу понять в чем дело.

+
ALTER PROCEDURE [dbo].[proc_update_csv_clinical]
AS

SET NOCOUNT ON
BEGIN

;
WITH
		T1 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(17, 426)
		)
UPDATE	T1 
		SET	T1.pasuxi = C.F41*0.1
		FROM T1
		INNER JOIN csv1 C 
			ON T1.momartvis_tarigi = C.F2 AND T1.id_clinical = C.F9
GO	

;
WITH
		T2 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(18, 427)
		)
UPDATE	T2 
		SET T2.pasuxi = C.F39*0.01
		FROM T2
		INNER JOIN csv1 C 
			ON T2.momartvis_tarigi = C.F2 AND T2.id_clinical = C.F9 
GO

;
WITH
		T3 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(19, 428)
		)
UPDATE	T3 
		SET T3.pasuxi = C.F43*0.1
		INNER JOIN csv1 C 
		FROM T3
			ON T3.momartvis_tarigi = C.F2 AND T3.id_clinical = C.F9 
GO

;
WITH
		T4 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(20, 429)
		)
UPDATE	T4 
		SET T4.pasuxi = C.F45*0.1
		FROM T4
		INNER JOIN csv1 C 
			ON T4.momartvis_tarigi = C.F2 AND T4.id_clinical = C.F9 
GO

;
WITH		 
		T5 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(21, 430)
		)
UPDATE	T5 
		SET T5.pasuxi = C.F47*0.1
		FROM T5
		INNER JOIN csv1 C 
			ON T5.momartvis_tarigi = C.F2 AND T5.id_clinical = C.F9 
GO

;
WITH		 
		T6 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(22, 431)
		)
UPDATE	T6 
		SET T6.pasuxi = C.F49*0.1
		FROM T6
		INNER JOIN csv1 C 
			ON T6.momartvis_tarigi = C.F2 AND T6.id_clinical = C.F9 
GO

;
WITH
		T8 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(24, 433)
		)
UPDATE	T8 
		SET T8.pasuxi = C.F51
		FROM T8
		INNER JOIN csv1 C 
			ON T8.momartvis_tarigi = C.F2 AND T8.id_clinical = C.F9 
GO

;
WITH
		T9 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(25, 434)
		)
UPDATE	T9 
		SET T9.pasuxi = C.F63*0.01
		FROM T9
		INNER JOIN csv1 C 
			ON T9.momartvis_tarigi = C.F2 AND T9.id_clinical = C.F9 
GO

;
WITH
		T10 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(26, 435)
		)
UPDATE	T10 
		SET T10.pasuxi = C.F37*0.01
		FROM T10
		INNER JOIN csv1 C 
			ON T10.momartvis_tarigi = C.F2 AND T10.id_clinical = C.F9 
GO

;
WITH
		T11 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(27, 436)
		)
UPDATE	T11 
		SET T11.pasuxi = C.F75*0.1
		FROM T11
		INNER JOIN csv1 C 
			ON T11.momartvis_tarigi = C.F2 AND T11.id_clinical = C.F9 
GO

;
WITH
		T13 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(28, 437)
		)
UPDATE	T13 
		SET T13.pasuxi = C.F77*0.1
		FROM T13
		INNER JOIN csv1 C 
			ON T13.momartvis_tarigi = C.F2 AND T13.id_clinical = C.F9 
GO

;
WITH
		T15 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(29, 438)
		)
UPDATE	T15 
		SET T15.pasuxi = C.F79*0.1
		FROM T15
		INNER JOIN csv1 C 
			ON T15.momartvis_tarigi = C.F2 AND T15.id_clinical = C.F9 
GO

;
WITH
		T17 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(30, 439)
		)
UPDATE	T17 
		SET T17.pasuxi = C.F81*0.1
		FROM T17
		INNER JOIN csv1 C 
			ON T17.momartvis_tarigi = C.F2 AND T17.id_clinical = C.F9 
GO

;
WITH		 
		T19 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(31, 440)
		)
UPDATE	T19 
		SET T19.pasuxi = C.F83*0.1
		INNER JOIN csv1 C 
			ON T19.momartvis_tarigi = C.F2 AND T19.id_clinical = C.F9 
GO

;
WITH
		T12 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(32, 441)
		)
UPDATE	T12 
		SET T12.pasuxi = C.F65*0.01
		FROM T12
		INNER JOIN csv1 C 
			ON T12.momartvis_tarigi = C.F2 AND T12.id_clinical = C.F9 
GO

;
WITH
		T14 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(33, 442)
		)
UPDATE	T14 
		SET T14.pasuxi = C.F67*0.01
		INNER JOIN csv1 C 
			ON T14.momartvis_tarigi = C.F2 AND T14.id_clinical = C.F9 
GO

;
WITH
		T16 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(34, 443)
		)
UPDATE	T16 
		SET T16.pasuxi = C.F69*0.01
		FROM T16
		INNER JOIN csv1 C 
			ON T16.momartvis_tarigi = C.F2 AND T16.id_clinical = C.F9 
GO

;
WITH
		T18 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(35, 444)
		)
UPDATE	T18 
		SET T18.pasuxi = C.F71*0.01
		FROM T18
		INNER JOIN csv1 C 
			ON T18.momartvis_tarigi = C.F2 AND T18.id_clinical = C.F9 
GO

;
WITH
		T20 as (
			SELECT	id_clinical, id_test, pasuxi, momartvis_tarigi
			FROM	baza_patient_tests BP
					RIGHT JOIN patients_history PH
						ON BP.id_patient_history = PH.id_patient_history
			WHERE id_test in(36, 445)
		)
UPDATE	T20 
		SET T20.pasuxi = C.F73*0.01
		FROM T20
		INNER JOIN csv1 C 
			ON T20.momartvis_tarigi = C.F2 AND T20.id_clinical = C.F9
END 


Выдаются ошибки.

+
Msg 102, Level 15, State 1, Procedure proc_update_csv_clinical, Line 20
Incorrect syntax near 'F9'.

(39 row(s) affected)
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'INNER'.

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)

(39 row(s) affected)
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'INNER'.

(39 row(s) affected)
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'INNER'.

(39 row(s) affected)

(39 row(s) affected)
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'END'.
16 июл 17, 15:55    [20648190]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2867
GO заменила на BEGIN ... END

+
USE [PPP]
GO
/****** Object: StoredProcedure [dbo].[proc_update_csv_clinical] Script Date: 7/16/2017 4:07:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_update_csv_clinical]
AS

SET NOCOUNT ON
BEGIN
;
WITH
T1 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(17, 426)
)
UPDATE T1
SET T1.pasuxi = C.F41*0.1
FROM T1
INNER JOIN csv1 C
ON T1.momartvis_tarigi = C.F2 AND T1.id_clinical = C.F9
END

BEGIN
;
WITH
T2 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(18, 427)
)
UPDATE T2
SET T2.pasuxi = C.F39*0.01
FROM T2
INNER JOIN csv1 C
ON T2.momartvis_tarigi = C.F2 AND T2.id_clinical = C.F9
END

BEGIN
;
WITH
T3 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(19, 428)
)
UPDATE T3
SET T3.pasuxi = C.F43*0.1
INNER JOIN csv1 C
FROM T3
ON T3.momartvis_tarigi = C.F2 AND T3.id_clinical = C.F9
END

BEGIN
;
WITH
T4 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(20, 429)
)
UPDATE T4
SET T4.pasuxi = C.F45*0.1
FROM T4
INNER JOIN csv1 C
ON T4.momartvis_tarigi = C.F2 AND T4.id_clinical = C.F9
END

BEGIN
;
WITH
T5 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(21, 430)
)
UPDATE T5
SET T5.pasuxi = C.F47*0.1
FROM T5
INNER JOIN csv1 C
ON T5.momartvis_tarigi = C.F2 AND T5.id_clinical = C.F9
END

BEGIN
;
WITH
T6 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(22, 431)
)
UPDATE T6
SET T6.pasuxi = C.F49*0.1
FROM T6
INNER JOIN csv1 C
ON T6.momartvis_tarigi = C.F2 AND T6.id_clinical = C.F9
END

BEGIN
;
WITH
T8 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(24, 433)
)
UPDATE T8
SET T8.pasuxi = C.F51
FROM T8
INNER JOIN csv1 C
ON T8.momartvis_tarigi = C.F2 AND T8.id_clinical = C.F9
END

BEGIN
;
WITH
T9 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(25, 434)
)
UPDATE T9
SET T9.pasuxi = C.F63*0.01
FROM T9
INNER JOIN csv1 C
ON T9.momartvis_tarigi = C.F2 AND T9.id_clinical = C.F9
END

BEGIN
;
WITH
T10 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(26, 435)
)
UPDATE T10
SET T10.pasuxi = C.F37*0.01
FROM T10
INNER JOIN csv1 C
ON T10.momartvis_tarigi = C.F2 AND T10.id_clinical = C.F9
END

BEGIN
;
WITH
T11 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(27, 436)
)
UPDATE T11
SET T11.pasuxi = C.F75*0.1
FROM T11
INNER JOIN csv1 C
ON T11.momartvis_tarigi = C.F2 AND T11.id_clinical = C.F9
END

BEGIN
;
WITH
T13 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(28, 437)
)
UPDATE T13
SET T13.pasuxi = C.F77*0.1
FROM T13
INNER JOIN csv1 C
ON T13.momartvis_tarigi = C.F2 AND T13.id_clinical = C.F9
END

BEGIN
;
WITH
T15 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(29, 438)
)
UPDATE T15
SET T15.pasuxi = C.F79*0.1
FROM T15
INNER JOIN csv1 C
ON T15.momartvis_tarigi = C.F2 AND T15.id_clinical = C.F9
END

BEGIN
;
WITH
T17 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(30, 439)
)
UPDATE T17
SET T17.pasuxi = C.F81*0.1
FROM T17
INNER JOIN csv1 C
ON T17.momartvis_tarigi = C.F2 AND T17.id_clinical = C.F9
END

BEGIN
;
WITH
T19 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(31, 440)
)
UPDATE T19
SET T19.pasuxi = C.F83*0.1
INNER JOIN csv1 C
ON T19.momartvis_tarigi = C.F2 AND T19.id_clinical = C.F9
END

BEGIN
;
WITH
T12 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(32, 441)
)
UPDATE T12
SET T12.pasuxi = C.F65*0.01
FROM T12
INNER JOIN csv1 C
ON T12.momartvis_tarigi = C.F2 AND T12.id_clinical = C.F9
END

BEGIN
;
WITH
T14 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(33, 442)
)
UPDATE T14
SET T14.pasuxi = C.F67*0.01
INNER JOIN csv1 C
ON T14.momartvis_tarigi = C.F2 AND T14.id_clinical = C.F9
END

BEGIN
;
WITH
T16 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(34, 443)
)
UPDATE T16
SET T16.pasuxi = C.F69*0.01
FROM T16
INNER JOIN csv1 C
ON T16.momartvis_tarigi = C.F2 AND T16.id_clinical = C.F9
END

BEGIN
;
WITH
T18 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(35, 444)
)
UPDATE T18
SET T18.pasuxi = C.F71*0.01
FROM T18
INNER JOIN csv1 C
ON T18.momartvis_tarigi = C.F2 AND T18.id_clinical = C.F9
END

BEGIN
;
WITH
T20 as (
SELECT id_clinical, id_test, pasuxi, momartvis_tarigi
FROM baza_patient_tests BP
RIGHT JOIN patients_history PH
ON BP.id_patient_history = PH.id_patient_history
WHERE id_test in(36, 445)
)
UPDATE T20
SET T20.pasuxi = C.F73*0.01
FROM T20
INNER JOIN csv1 C
ON T20.momartvis_tarigi = C.F2 AND T20.id_clinical = C.F9
END


Теперь осталось только с INNER разобраться
ОШИБКА

Msg 156, Level 15, State 1, Procedure proc_update_csv_clinical, Line 51
Incorrect syntax near the keyword 'INNER'.
Msg 156, Level 15, State 1, Procedure proc_update_csv_clinical, Line 238
Incorrect syntax near the keyword 'INNER'.
Msg 156, Level 15, State 1, Procedure proc_update_csv_clinical, Line 271
Incorrect syntax near the keyword 'INNER'.
16 июл 17, 16:12    [20648216]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2867
Нашла ошибку, где-то не дописала, а где-то местами перепутала ... :)
16 июл 17, 16:55    [20648263]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить