Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Expert help needed on update where exists syntax.

Posted on 2015-02-02
9
87 Views
Last Modified: 2015-02-04
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
Comment
Question by:brgdotnet
9 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40585517
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 40585521
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40585525
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40585552
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40585676
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
 
LVL 32

Expert Comment

by:awking00
ID: 40588628
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40589573
whether CTE or not, you still require a JOIN

because you reference columns from another table inside the SET
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40590236
Thank you, not using a where exists was the best approach.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question