April 9, 2019 at 9:44 am
Hi,
I have a requirement to insert values in the Comments column based on meeting certain conditions. If there are multiple conditions then multiple inserts need to be made in the Comments column
The issue faced is if there are multiple comments to be added, then only the recent update works.
I need to make the below code work:
-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
IF _APP_CDE IN ('ARCH_WEEK', 'ARCH_ACCOUNTS') THEN
CREATE TEMP TABLE TEST AS
SELECT DISTINCT A.ISBN,A.WEEK_NUMBER AS CURRENT_WEEK ,T.WEEK_NUMBER AS PREVIOUS_WEEK,A.AUTHOR AS CURRENT_AUTHOR, T.AUTHOR AS PREVIOUS_AUTHOR
FROM ARCH_MARKET A
INNER JOIN
(SELECT ISBN,MAX(WEEK_NUMBER) WEEK_NUMBER FROM ARCH_MARKET GROUP BY ISBN) MA ON A.ISBN=MA.ISBN AND A.WEEK_NUMBER=MA.WEEK_NUMBER INNER JOIN
ARCH_MARKET T ON A.ISBN =T.ISBN AND A.AUTHOR <> T.AUTHOR INNER JOIN
(SELECT ISBN,MAX(NEXT_WEEK_NUMBER) NEXT_WEEK_NUMBER
FROM (SELECT ISBN,WEEK_NUMBER,LAG(WEEK_NUMBER) OVER (PARTITION BY ISBN ORDER BY WEEK_NUMBER) NEXT_WEEK_NUMBER
FROM ARCH_MARKET) A
GROUP BY ISBN) NT ON T.ISBN=NT.ISBN AND T.WEEK_NUMBER = NT.NEXT_WEEK_NUMBER;
UPDATE ARCH_MARKET ARCH
SET IS_VALID = 'N'
, COMMENTS = 'Author changed from previous week...'
FROM TEST CUR
WHERE ARCH.ISBN=CUR.ISBN AND ARCH.WEEK_NUMBER=CUR.PREVIOUS_WEEK AND ARCH.AUTHOR=CUR.PREVIOUS_AUTHOR;
DROP TABLE TEST;
CREATE TEMP TABLE TEST AS
SELECT DISTINCT A.ISBN,A.WEEK_NUMBER AS CURRENT_WEEK ,T.WEEK_NUMBER AS PREVIOUS_WEEK,A.TITLE AS CURRENT_TITLE, T.TITLE AS PREVIOUS_TITLE
FROM ARCH_MARKET A
INNER JOIN
(SELECT ISBN,MAX(WEEK_NUMBER) WEEK_NUMBER FROM ARCH_MARKET GROUP BY ISBN) MA ON A.ISBN=MA.ISBN AND A.WEEK_NUMBER=MA.WEEK_NUMBER INNER JOIN
ARCH_MARKET T ON A.ISBN =T.ISBN AND A.TITLE <> T.TITLE INNER JOIN
(SELECT ISBN,MAX(NEXT_WEEK_NUMBER) NEXT_WEEK_NUMBER
FROM (SELECT ISBN,WEEK_NUMBER,LAG(WEEK_NUMBER) OVER (PARTITION BY ISBN ORDER BY WEEK_NUMBER) NEXT_WEEK_NUMBER
FROM ARCH_MARKET) A
GROUP BY ISBN) NT ON T.ISBN=NT.ISBN AND T.WEEK_NUMBER = NT.NEXT_WEEK_NUMBER;
UPDATE ARCH_MARKET ARCH
SET IS_VALID = 'N'
, COMMENTS = 'Title changed from previous week...'
FROM TEST CUR
WHERE ARCH.ISBN=CUR.ISBN AND ARCH.WEEK_NUMBER=CUR.PREVIOUS_WEEK AND ARCH.TITLE=CUR.PREVIOUS_TITLE;
DROP TABLE TEST;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
The result I need to get is Comments column should display both the comments from the above query like below if both the above conditions are met:
Author changed from previous week...Title changed from previous week...
Any help would be appreciated. Thanks.
April 9, 2019 at 1:42 pm
You don't need an insert, you need an update. However, your update needs to include a concatenation of the previous contents.
Something like:
create table #mytable(myid int, mycomment varchar(200))
insert ##mytable (myid, mycomments) values(1, ''), (2, '')
go
upate #mytable
set mycomments = mycomments + '..Author edited'
where myid = 1
go
upate #mytable
set mycomments = mycomments + '..Title edited'
where myid = 1
go
select * from #mytable
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply