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  )
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pratima PharandeConnect With a Mentor Commented:
Try this

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

From SETTINGS S
 INNER JOIN USERS PSUS
       ON PSUS.PSUS_USER_NUM = S.ACCOUNT_NUM
       INNER JOIN STAGE STG
       ON STG.PSUS_USER_ID = PSUS.PSUS_USER_ID
       WHERE S.ID = 'I'
0
 
Pratima PharandeCommented:
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'
     )  
 )
0
 
PortletPaulfreelancerCommented:
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Pratik MakwanaData AnalystCommented:
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'
)  
)
0
 
PortletPaulfreelancerCommented:
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

0
 
awking00Commented:
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';
0
 
PortletPaulfreelancerCommented:
whether CTE or not, you still require a JOIN

because you reference columns from another table inside the SET
0
 
brgdotnetcontractorAuthor Commented:
Thank you, not using a where exists was the best approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.