Update column in table + row by row

[kemorr]

Hi everyone,

Firstly, Like the new look, very nice!

I have a table [dbo].[tbl_Discharge] with a column call [Ward] at the moment the column contains data like F1*WSH and PACU*WSH etc, I am trying to update the column data to all char to the left of the * (* appears in different posistions)

I only want to carry out this update on rows where [download_date] is > Date()

Here's where I'm at so far but it's not working to well, I was using the PRINT function to display the results before I commit to executing the code on the actual data.

Many Thanks

Declare @NewWard nvarchar(15),
			@Ward nvarchar(15),	  
			@Date datetime,
			@Len int,
			@i int

SET @Ward = (SELECT [Ward] From [dbo].[tbl_Discharge]
WHERE [Download_Date] >= DATEDIFF(dd,0,GETDATE()))

SET @Date = DATEDIFF(dd,0,GETDATE())
SET @i = 0
SET @Len = Len(@Ward)

While @i < @Len + 1

		If Right(Left(@Ward,@i),1) = '*'
			SET @NewWard = Left(@Ward,@i-1)

			PRINT 'Old Ward Name = ' + @Ward + 
			', new ward name = ' + @NewWard + 
			', Download Date = ' @Date

SET @i = @i + 1
GO

[HansV]

Why do you use DATEDIFF here?

Take a look at the CHARINDEX function.

[kemorr]

[quote name='HansV' post='761779' date='26-Feb-09 12:02']Why do you use DATEDIFF here?

Take a look at the CHARINDEX function.[/quote]

Thanks Hans,

Thats cut down on some code, but I still can't get it to work, it only works if there is only one row that matches the criteria I can't see to get it to cycle through all the rows that meet the select statement.

In access I was using rst (recordset) for example; (air code)

Dim strNewWard As String
Dim rst as DAO.RecordSet
Dim dbs as DAO.Database
Dim i As integar
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordSet(SELECT ([Ward]) FROM tbl_Discharge WHERE [Download_Date] >= Date())

   Do While Not rst.EOF
	   For i = 1 to Len(rst!Ward)
		 If Right(Left(rst!Ward,i),1)="*" Then
			strNewWard = Left(rst!Ward,i-1)
		 End If
		 rst.Update
		 rst.Next
	   Next i

This is where I'm at now, any ideas where I'm going wrong.

CREATE PROCEDURE [dbo].[sp_Update_tbl_Discharge_Ward] 
			(@NewWard nvarchar(15),
			@Ward nvarchar(15),	  
			@Date datetime)

AS

SET @Ward = (SELECT [Ward] From [dbo].[tbl_Discharge]
WHERE [Download_Date] >= DATEDIFF(dd,0,GETDATE()))

SET @NewWard = Left(@Ward,CHARINDEX('*',@Ward)-1)

UPDATE [dbo].[tbl_Discharge]

SET [Ward] = @NewWard
GO

[HansV]

Why not run an update sql statement?

UPDATE [dbo].[tbl_Discharge] SET [Ward] = Left([Ward],CHARINDEX('*',@Ward)-1) WHERE [Download_Date] >= GETDATE()

[kemorr]

[quote name='HansV' post='761791' date='26-Feb-09 14:59']Why not run an update sql statement?

UPDATE [dbo].[tbl_Discharge] SET [Ward] = Left([Ward],CHARINDEX('*',@Ward)-1) WHERE [Download_Date] >= GETDATE()[/quote]

Many thanks Hans, Works great.