July 16, 2015 at 7:55 am
Greetings -
I have a query as following:
** Query***
With CTE1 (col1, col2, col3)
AS
(SELECT COL1, COL2, COL3
FROM TBL1 JOIN TBL2)
SELECT A.A, A.B, B.COL1, B.COL2
FROM TBL4 A JOIN CTE1 B ON A.A =B.COL1
I want to do update:
UPDATE fromabovequery
SET A.A = B.COL3
How would you give the query an alias so it can be used in the update statement, In another words how you would update A.S to be equal to a value from the CTE result.
Thanks,
July 16, 2015 at 8:13 am
lsalih (7/16/2015)
Greetings -I have a query as following:
** Query***
With CTE1 (col1, col2, col3)
AS
(SELECT COL1, COL2, COL3
FROM TBL1 JOIN TBL2)
SELECT A.A, A.B, B.COL1, B.COL2
FROM TBL4 A JOIN CTE1 B ON A.A =B.COL1
I want to do update:
UPDATE fromabovequery
SET A.A = B.COL3
How would you give the query an alias so it can be used in the update statement, In another words how you would update A.S to be equal to a value from the CTE result.
Thanks,
How about:
WITH CTE1 (col1, col2, col3) AS (
SELECT COL1, COL2, COL3
FROM TBL1 T1
JOIN TBL2 T2
)
UPDATE A
SET A.A = B.col3
FROM TBL4 A
JOIN CTE1 B
ON A.A = B.COL1
It doesn't appear to need to be any more complicated than that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2015 at 8:28 am
Hi Steve -
Following is how I have the query, I first have a set of data from the CTE, then another query that is joined to the CTE. What I want to do is to update addr.PAR_ADDRESS to STU_ADDRESSGU from below query resultset.
with CTE_STU (EMPGU, EM_NUM, STU_STATUS, par_gu, STU_ADDRESS, STU_CITY
, STU_STATE, STU_ZIP, STU_ADDRESSGU)
as
(SELECT distinct
stu.EMPGU as EMPGU, stu.EM_NUM as EM_NUM, essy.STATUS as STU_STATUS, par.PARGU as PAR_GU
, addr.address as STU_ADDRESS, addr.city as STU_CITY
, addr.state as STU_STATE, addr.zip_5 as STU_ZIP, addr.LOCATION as STU_ADDRESSGU
FROM
EMPL stu join
YEAR ESSY ON STU.EMPGU = ESSY.EMPGU join
PERSON stuper on stuper.PARGU = stu.EMPGU join
EMPL_PARENT par on par.EMPGU = stu.EMPGU join
LOCATI addr on stuper.HOME_LOCATION = addr.LOCATION
)
SELECT DISTINCT
stu.EM_NUM, stuper.LAST_NAME, stuper.FIRST_NAME , org.ORGANIZATION_ABBR_NAME as SCH, tblgrd.VALUE_DESCRIPTION as TEST
,yr.school_year, stupar.LIVES_WITH
, cast (stupar.COMMENTS as NVARCHAR(MAX)) as COMMENTS, par.last_name as P_Lname, par.first_name as P_Fname , stupar.orderby,
addr.[address], addr.CITY, addr.[STATE], ADDR.ZIP_5
STU_STATUS, STU_ADDRESS, STU_CITY, STU_STATE, STU_ZIP, addr.PAR_ADDRESS, STU_ADDRESSGU
FROM
EMPL_PARENT stupar left join
EMPL stu on stu.EMPGU = stupar.EMPGU and stupar.LIVES_WITH = 'Y' join
YEAR ESSY ON STU.EMPGU = ESSY.EMPGU and (essy.enter_date IS NOT NULL AND essy.INFO IS NULL AND ABCIS NULL) join
(select YRGU, school_year from YEARS where YEAR in (2012, 2013)) YR on YR.YRGU = essy.YRGU join
ORGYEAR ROY on roy.ORGANIZATION_YRGU = ESSY.ORGANIZATION_YRGU join
WORK org on org.WORKGU = roy.WORKGU join
INST ESCH ON ESCH.WORKGU = roy.WORKGU join
rev.EPC_PARENT PARNT on parnt.PARGU = stupar.PARGU join
LOOKUP('S', 'TEST') AS tblgrd ON tblgrd.VALUE_CODE = ESSY.TEST join
PERSON par on par.PARGU = stupar.PARGU and par.home_LOCATION is null join
PERSON stuper on stuper.PARGU = stu.EMPGU left join
(select LOCATION as PAR_ADDRESS,[address], city, [STATE], ZIP_5 from LOCATI) addr on addr.PAR_ADDRESS = par.HOME_LOCATIONjoin
CTE_STU CTE on CTE.par_gu = PARNT.PARGU
July 16, 2015 at 8:30 am
I just changed my query to follow your example, and it worked. Thank you. I appreciate it.
July 16, 2015 at 6:28 pm
lsalih (7/16/2015)
I just changed my query to follow your example, and it worked. Thank you. I appreciate it.
You're welcome. Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2015 at 6:33 pm
Once again, many thanks Steve.
I read online the examples but your example was very clear, I immediately realized how to do it... Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply