Solved

Expert help needed on update where exists syntax.

Posted on 2015-02-02
9
90 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
[X]
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
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 49

Expert Comment

by:PortletPaul
ID: 40585525
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

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 49

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

691 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