Updation of Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updation of Data

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 03:02:38

DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')

DECLARE @Incident_ID VARCHAR(8000)
SET @Incident_ID=''
SELECT @Incident_ID=ID+','+@Incident_ID
FROM @INCIDENT WHERE CONVERT(VARCHAR(10),DUE_DATE,101)=CONVERT(VARCHAR(10),GETDATE(),101)
AND DATEPART(MINUTE,Due_DATE)=DATEPART(minute,GETDATE())

SELECT @incident_ID

UPDATE @INCIDENT SET Status ='InActive'
WHERE ID IN (SELECT @Incident_ID FROM @Incident
WHERE ','+ @Incident_ID +',' LIKE (SELECT '%,'+ @Incident_ID + ',%'))
select * from @incident

Hi When i use the Following Query the values in @Incident Table are not updated to Inactive. Can anyone please tell me why is the abve Query , despite of the variable @Incident haveing the value as '2,1,'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 03:17:33

try this once

DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')
insert into @incident values ('3',getdate()-3,'Active')

DECLARE @Incident_ID VARCHAR(8000)
SET @Incident_ID=''
SELECT @Incident_ID= stuff((select ','+id
FROM @INCIDENT WHERE dateadd(dd,datediff(dd,0,Due_DATE),0)=dateadd(dd,datediff(dd,0,getdate()),0)
AND DATEPART(MINUTE,Due_DATE)=DATEPART(minute,GETDATE()) for xml path('')),1,1,'')

SELECT @incident_ID

UPDATE @INCIDENT SET Status ='InActive'
WHERE '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%'

select * from @incident

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 05:22:49

Thanks for the reply. but when i execute it i get the following error

Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'xml'.

I dont have any idea about this error..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 05:29:20

quote:
Originally posted by swathigardas

Thanks for the reply. but when i execute it i get the following error

Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'xml'.

I dont have any idea about this error..

no its working for me r u sql2005 r not
DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')
insert into @incident values ('3',getdate()-3,'Active')

DECLARE @Incident_ID VARCHAR(8000)
SET @Incident_ID=''
SELECT @Incident_ID= stuff((select ','+id
FROM @INCIDENT WHERE dateadd(dd,datediff(dd,0,Due_DATE),0)=dateadd(dd,datediff(dd,0,getdate()),0)
AND DATEPART(MINUTE,Due_DATE)=DATEPART(minute,GETDATE()) for xml path('')),1,1,'')

SELECT @incident_ID

UPDATE @INCIDENT SET Status ='InActive'
WHERE '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%'

select * from @incident

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 05:30:26

yes i use sql 2005

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 05:36:19

quote:
Originally posted by swathigardas

yes i use sql 2005

then it will works fine

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 05:50:16

i dont have any idea why it dint work.
i tried like this from your code which worked for updation.
But it is not working for insertion of data into another table

DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')

DECLARE @Incident_ID VARCHAR(8000)
SET @Incident_ID=''
SELECT @Incident_ID=ID+','+@Incident_ID
FROM @INCIDENT WHERE CONVERT(VARCHAR(10),DUE_DATE,101)=CONVERT(VARCHAR(10),GETDATE(),101)
AND DATEPART(MINUTE,Due_DATE)=DATEPART(minute,GETDATE())

SELECT @incident_ID
UPDATE @INCIDENT SET Status ='InActive'
WHERE '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%'
select * from @incident

declare @incident_status table (id varchar(2), status_id varchar(2),status_from varchar(15), status_to varchar(15))
insert into @incident_status values (1,1,'Active','Inactive')
insert into @incident_status values (1,2,'Inactive','Active')
insert into @incident_Status values (2,1,'Active','Inactive')
insert into @incident_Status values (2,2,'Inactive','Active')

insert into @incident_status (id,status_id,status_from,status_to)
select @incident_id,max(status_id)+1,status_to,'Inactive' From @incident_status where '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%' group by status_to
select * from @incident_status

When i try to insert data into @incident_status table it shows the following error -

Msg 8152, Level 16, State 9, Line 25
String or binary data would be truncated.
The statement has been terminated.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 05:57:41

quote:
Originally posted by swathigardas

i dont have any idea why it dint work.
i tried like this from your code which worked for updation.
But it is not working for insertion of data into another table

DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')

DECLARE @Incident_ID VARCHAR(8000)
SET @Incident_ID=''
SELECT @Incident_ID=ID+','+@Incident_ID
FROM @INCIDENT WHERE CONVERT(VARCHAR(10),DUE_DATE,101)=CONVERT(VARCHAR(10),GETDATE(),101)
AND DATEPART(MINUTE,Due_DATE)=DATEPART(minute,GETDATE())

SELECT @incident_ID
UPDATE @INCIDENT SET Status ='InActive'
WHERE '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%'
select * from @incident

declare @incident_status table (id varchar(10), status_id varchar(2),status_from varchar(15), status_to varchar(15))
insert into @incident_status values (1,1,'Active','Inactive')
insert into @incident_status values (1,2,'Inactive','Active')
insert into @incident_Status values (2,1,'Active','Inactive')
insert into @incident_Status values (2,2,'Inactive','Active')

insert into @incident_status (id,status_id,status_from,status_to)
select @incident_id,max(status_id)+1,status_to,'Inactive' From @incident_status where '%,'+ @Incident_ID +',%' LIKE '%,'+ id + ',%' group by status_to
select * from @incident_status

When i try to insert data into @incident_status table it shows the following error -

Msg 8152, Level 16, State 9, Line 25
String or binary data would be truncated.
The statement has been terminated.

increase the length of varchar fields in @incident_status

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 06:09:41

But i want individual records to be inserted in incident status when incident_id varibale has value

as per the above example incident status table should be inserted with two rows and should have data like this after insertion

Id Status Id status_From Status_To
1 1 Active Inactive
1 2 Inactive Active
2 1 Active Inactive
2 2 Inactive Active
1 3 Active Inactive
2 3 Active Inactive

How to achieve this

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 06:25:23

DECLARE @incident table (id varchar(2) , due_date datetime,status varchar(15))

insert into @incident values ('1',getdate(),'Active')
insert into @incident values ('2',getdate(),'Active')

select * from @incident

declare @incident_status table (id varchar(10), status_id varchar(2),status_from varchar(15), status_to varchar(15))
insert into @incident_status values (1,1,'Active','Inactive')
insert into @incident_status values (1,2,'Inactive','Active')
insert into @incident_Status values (2,1,'Active','Inactive')
insert into @incident_Status values (2,2,'Inactive','Active')

insert into @incident_status (id,status_id,status_from,status_to)
select i.id,s.value,i.status,'Inactive' From
@incident i inner join (select id,max(status_id)+1 as value from @incident_status group by id )s on s.id = i.id
select * from @incident_status
try this

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-22 : 06:28:18

Thanks a lot for u r reply.. it works perfectly.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 06:39:49

quote:
Originally posted by swathigardas

Thanks a lot for u r reply.. it works perfectly.

ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources