August 12, 2009 at 8:10 am
I have two tables table A and Table B
Table A has a column foldername ,expirydate,finaldate
Table B has an associated column foldername
Table B has also a column called folderdate
based on a condition I need to change the expitydate and finaldate of folder
I tried the following syntax but couldnt obtain the results
DECLARE @date datetime
SELECT @date = folderdate from tableB B inner join TableA A
on A.foldername = B.foldername
where condition 1
Update folder
select expirydate = @date + 2
select finaldate = @date + 2
where condition
The above sytax assigns the last value from the subquery to
@date and all the all the expirydate and final
date are being updated by the same value but
not associated folderdate
I cannot use the following sytax
DECLARE @date datetime
SET @date = select folderdate from tableB B inner join TableA A
on A.foldername = B.foldername
where condition 1
Update folder
set expirydate = @date + 2
set finaldate = @date + 2
where condition
since the subquery returns more than one value.
How do I update expirydate ,finaldate with associated value from tableB
August 12, 2009 at 8:33 am
I think you are asking for something like this. I have created test tables and test data so if this is inaccurate let me know. This will change the 2 date fields in table A to be the folderDate field in table B based on the folderName.
CREATE TABLE #a(foldername VARCHAR(10), expiryDate DATETIME, finalDate DATETIME)
CREATE TABLE #b (foldername varchar(10), folderDate DATETIME)
INSERT INTO #a VALUES('test1',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test2',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test3',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test4',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test5',GETDATE(), GETDATE())
INSERT INTO #b VALUES('test1','01/01/09')
INSERT INTO #b VALUES('test2','02/01/09')
INSERT INTO #b VALUES('test3','03/01/09')
INSERT INTO #b VALUES('test4','04/01/09')
INSERT INTO #b VALUES('test5','05/01/09')
SELECT * FROM #a
UPDATE a
SET a.expiryDate =b.folderDate,
a.finalDate = b.folderDate
FROM #a a
INNER JOIN #b b ON (a.foldername = b.foldername)
SELECT * FROM #a
August 12, 2009 at 8:54 am
CREATE TABLE #a(foldername VARCHAR(10), expiryDate DATETIME, finalDate DATETIME)
CREATE TABLE #b (foldername varchar(10), folderDate DATETIME)
INSERT INTO #a VALUES('test1',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test2',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test3',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test4',GETDATE(), GETDATE())
INSERT INTO #a VALUES('test5',GETDATE(), GETDATE())
INSERT INTO #b VALUES('test1','01/01/09')
INSERT INTO #b VALUES('test2','02/01/09')
INSERT INTO #b VALUES('test3','03/01/09')
INSERT INTO #b VALUES('test4','04/01/09')
INSERT INTO #b VALUES('test5','05/01/09')
SELECT * FROM #a
UPDATE a
SET a.expiryDate =b.folderDate,
a.finalDate = b.folderDate
FROM #a a
INNER JOIN #b b ON (a.foldername = b.foldername)
There are distinct expirydate and finaldate values in #a for everyfoldername in #b.
So if a condition is satisfied, I need to change the folderdate in #b with (finaldate +2) present in #a
for example :
if folder A in #b has a final date of 01/02/09
folder B in #b has a final date of 02/03/09
I need to change the value in #a with final date + 2
I have to go through each and every record to make the correspoding change in table#b based on values in #a
I was wondering if this is where Cursor needs to be used to loop though each and every record to do the updates.
August 12, 2009 at 9:32 am
I really don't think you will have to use a cursor for this. I am still not sure about the statements below. What condition has to be satisfied? Are you adding 2 days, months, or years to the finalDate value? I am not sure about the logic in your example either.
IF folderA in #b has finalDate = '01/02/09'
THEN "what happens"
So if a condition is satisfied, I need to change the folderdate in #b with (finaldate +2) present in #a
for example :
if folder A in #b has a final date of 01/02/09
folder B in #b has a final date of 02/03/09
I need to change the value in #a with final date + 2
August 12, 2009 at 11:45 am
Here is an example:
Table A
------------------------------------
Foldername expirydate finaldate
a 01/02/09 01/03/09
b 02/05/09 02/06/09
c 02/05/10 02/05/11
a 02/05/11 02/05/12
Table B
---------------------------------------
Foldername date code
a 02/05/09 3
b 02/05/09 5
c 02/05/09 7
If the code is 3
then the all the expiry date and final date for folder a should be changed to date + 2
There are multiple entries for foldername in TableA.
Hope I was clear. Thanks.
August 12, 2009 at 12:01 pm
Lets see if this is closer. The code has a couple comments but I will try to expand. The dateadd function takes 3 parameters (datepart, number, start date). I have used days as the date part but you can change that to whatever you need. Lookup dateadd in books online.
I have joined to the table with your criteria of code = 3 so you can also change that based on what you need.
Let me know if this gets you where you need to be.
CREATE TABLE #a(foldername VARCHAR(10), expiryDate DATETIME, finalDate DATETIME)
CREATE TABLE #b (foldername varchar(10), folderDate DATETIME, code int)
INSERT INTO #a VALUES('a','01/02/09', '01/03/09')
INSERT INTO #a VALUES('b','02/05/09', '02/06/09')
INSERT INTO #a VALUES('c','02/05/09', '02/05/11')
INSERT INTO #a VALUES('a','02/11/09', '02/05/12')
INSERT INTO #b VALUES('a','02/05/09', 3)
INSERT INTO #b VALUES('b','02/05/09', 5)
INSERT INTO #b VALUES('c','02/05/09', 7)
SELECT * FROM #a
UPDATE a
SET a.expiryDate = DATEADD(d,2,b.folderDate), --used to add 2 days to date in table b
a.finalDate = DATEADD(d,2,b.folderDate) --used to add 2 days to date in table b
FROM #a a
INNER JOIN #b b ON (a.foldername = b.foldername AND b.code = 3) --specifies the code
SELECT * FROM #a
DROP TABLE #a, #b
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply