• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

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  )
0
brgdotnet
Asked:
brgdotnet
1 Solution
 
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
 
Pratima PharandeCommented:
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
 
PortletPaulCommented:
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
whether CTE or not, you still require a JOIN

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now