I've got a very simple SQL update to do as part of a SSIS package we're using and i'm using an OLE DB Command task toperform the update.
The update statement is
UPDATE TableName SET Field1 = ? WHERE Field2 = ?
The problem is one of the values for field 2 is NULL. however records where Field 2 IS NULL aren't getting updated. I tried changing the SQL to
UPDATE TableName SET Field1 = ? WHERE Field2 = ? OR (? IS NULL AND Field2 IS NULL)
however it wont let me map the same input colum to multiple parameters, and duplicating input columns with a derived column seems a bit of a fudge
I must have spent a good hour or 2 searching for a solution but to no avail.
If you want to use the same parameter twice in an update operation in an OLE DB Command, you need to make your SQL a little more complex and introduce some variables. The following ought to work better - of course, you'll have to adjust to use the correct data types.
DECLARE @Field1 AS CHAR(10) DECLARE @Field2 AS CHAR(20) SET @Field1 = ? SET @Field2 = ? UPDATE TableName SET Field1 = @Field1 WHERE Field2 = @Field2 OR (@Field2 IS NULL AND Field2 IS NULL)
Talk to me now on
Cheers for the reply Todd
Unfortunately i'm getting an error "Syntax error, permissions violation, or other non specific error"
basically as soon as i try and set the declared variables to the paramter values it errors, i've added the SQL a line at a time as a test and as soon as it gets to
SET @Field1 = ? it doesn't like it