Question IF..THEN with SQL UPDATE?

[Ian W]

I am trying to write an update query but I can't figure out how to do something.

Is it possible to have a update query look at a field and if it is Null update it but if not null skip it?

Basically have a date field that I want to insert todays date into unless it already has a date.

 

[r3plica]

This should work

  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

 

[rcombs4]

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

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

 

[Ian W]

I already have a WHERE clause in it though ?

Here is my current statement.

"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.

 

[InertiaM]

I *believe* this will work.

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

 

[r3plica]

try

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.

 

[r3plica]

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)

 

[Ian W]

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...