Solved

Expert help needed on update where exists syntax.

Posted on 2015-02-02
9
86 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Query link database loop 8 38
hyperlink data type in SQL 3 27
SQL Improvement  ( Speed) 14 27
Create snapshot on MSSQL 2012 3 18
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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