asked on

Expert help needed on update where exists syntax.

I am using MS Sql Server

My syntax on lines 2 and 3 below is incorrect. I get the error message : Incorrect syntax near the keyword 'set'.

How can I fix the sql below so that I can update SETTING.Stub = STG.Stub, and SETTINGS.SEC_LVL = STG.FACS_SEC_LVL ????

Here is my SQL :

1 UPDATE SETTINGS
2 set SETTINGS.Stub = STG.Stub,
3 set SETTING.SEC_LVL = STG.FACS_SEC_LVL
4 where exists
5 (
6 select ACCOUNT_NUM from SETTINGS WHERE EXISTS
7 (
8 select FACS.ACCOUNT_NUM, STG.FACS_SEC_LVL,STG.FPDA_ALLOWED_VALUE,STG.Stub
9 FROM SETTINGS FACS
10 INNER JOIN USERS PSUS
11 ON PSUS.PSUS_USER_NUM = FACS.ACCOUNT_NUM
12 INNER JOIN STAGE STG
13 ON STG.PSUS_USER_ID = PSUS.PSUS_USER_ID
14 WHERE FACS.ID = 'I'
15 )
16 )

Pratima

UPDATE S
set S.Stub = STG.Stub,
set S.SEC_LVL = STG.FACS_SEC_LVL

From SETTINGS S
where exists
(
select ACCOUNT_NUM from SETTINGS WHERE EXISTS
(
select FACS.ACCOUNT_NUM, STG.FACS_SEC_LVL,STG.FPDA_ALLOWED_VALUE,STG.Stub
FROM SETTINGS FACS
INNER JOIN USERS PSUS
ON PSUS.PSUS_USER_NUM = FACS.ACCOUNT_NUM
INNER JOIN STAGE STG
ON STG.PSUS_USER_ID = PSUS.PSUS_USER_ID
WHERE FACS.ID = 'I'
)
)

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

NB: You only need SET once

see: https://msdn.microsoft.com/en-us/library/ms177523.aspx

Pratik Makwana

UPDATE SETTINGS
set SETTINGS.Stub = STG.Stub,
SETTING.SEC_LVL = STG.FACS_SEC_LVL
where exists
(
select ACCOUNT_NUM from SETTINGS WHERE EXISTS
(
select FACS.ACCOUNT_NUM, STG.FACS_SEC_LVL,STG.FPDA_ALLOWED_VALUE,STG.Stub
FROM SETTINGS FACS
INNER JOIN USERS PSUS
ON PSUS.PSUS_USER_NUM = FACS.ACCOUNT_NUM
INNER JOIN STAGE STG
ON STG.PSUS_USER_ID = PSUS.PSUS_USER_ID
WHERE FACS.ID = 'I'
)
)

PortletPaul

I agree with ID: 40585521 (except you only need SET once)

You cannot reference the subquery contained within an EXISTS as part of the SET operation. You need to use a JOIN.

UPDATE SETTINGS
SET SETTINGS.Stub = STG.Stub , SETTING.SEC_LVL = STG.FACS_SEC_LVL

that alias STG cannot be used from an item inside the where clause

WHERE exists (....... inner join stage STG .... ) --<< that alias does NOT work at SET!

So, you MUST reference STG as a JOIN

UPDATE SETTINGS
SET SETTINGS.Stub = STG.Stub
  , SETTING.SEC_LVL = STG.FACS_SEC_LVL
FROM SETTINGS
INNER JOIN USERS PSUS
      ON PSUS.PSUS_USER_NUM = SETTINGS.ACCOUNT_NUM
INNER JOIN STAGE STG
      ON STG.PSUS_USER_ID = PSUS.PSUS_USER_ID
WHERE SETTINGS.ID = 'I'
;

Open in new window

awking00

You might try a common table expression update -
with cte as
(select stg.stub as stub, stg.facs_sec_lvl as facs_sec_lvl, u.psus_user_num as user_num
from stage as stg
inner join users as u
on stg.psus_user_id = u.psus_user_id)
update settings
set stub = cte.stub, sec_lvl = cte.facs_sec_lvl
from settings as s
inner join cte
on s.account_num = cte.user_num
where s.id = 'I';

PortletPaul

whether CTE or not, you still require a JOIN

because you reference columns from another table inside the SET

brgdotnet

ASKER

Thank you, not using a where exists was the best approach.