Thread: IF..THEN with SQL UPDATE?

This should work

Code:

  CREATE PROC dbo.UpdateDate
	@ID INT
  AS
  BEGIN
	  DECLARE @Date VARCHAR(100)
	  
	  SELECT @Date = CAST([Date] AS VARCHAR(100))
	  FROM tTable
	  WHERE ID = @ID
	  
	  IF @Date = ''
	  BEGIN
		UPDATE tTable
		SET [Date] = GETDATE()
		WHERE ID = @ID
	  END
  END
  GO

You can also just add a WHERE clause in your update query.

Code:

UPDATE MyTable
SET MyField = GetDate()
WHERE MyField IS NULL

I already have a WHERE clause in it though ?

Here is my current statement.

Code:

"UPDATE Serial_Data_Table SET SDT_PSS_IX = 4 WHERE SDT_Serial = '" & sn & "'"

So using the above example I want to update SDT_Date if the field is currently Null.

I *believe* this will work.

Code:

UPDATE
  Serial_Data_Table
SET
  Serial_Data_Table.SDT_PSS_IX = 4,
  Serial_Data_Table.SDT_Date = CASE
    WHEN Serial_Data_Table.SDT_Date IS NULL THEN @NEW_SDT_DATE /* NEW VALUE */
    ELSE Serial_Data_Table.SDT_Date END /* NO CHANGE */ 
WHERE
  Serial_Data_Table.SDT_Serial = @SDT_Serial

try

Code:

UPDATE Serial_Data_Table
SET 
	SDT_PSS_IX = 4,
	SDT_Date = 
	CASE 
		WHEN CAST(SDT_Date AS VARCHAR(100)) = '' THEN GETDATE()
		ELSE NULL
	END
WHERE SDT_Serial = @SN

I have used a parameter for your serial, you can use it the way you had if you like.

i find casting as a varchar and checking for an empty string for some reason works better than checking for NULLs.
That could just be me though.
Either solution posted will work (or should)

Many thanks for the replys.

I think I may have posted this in the wrong section though..

I'm using an MS Access backend and executing my queries using oledbcommand.ExecuteNonQuery.

I can't figure out how to get the examples that have been posted working.

Sorry, i'm new to .net...