Update Message Explination

Question

Update Message Explination

crazy_new

SSCarpal Tunnel

Points: 4804
More actions
May 6, 2015 at 2:42 am

#297949

Hi All,
I am trying to built in a rule that when data is imported and the invoice date is null, it should default to the day before's date.
I know for a fact there is no records where the invoice date is null, because I have executed the code that I have tested.
IF EXISTS(
SELECT
invoicedate
FROM
mpsales.it
WHERE invoicedate IS NULL
)
UPDATE
[MPSales].[IT]
SET
invoicedate = DATEADD(DAY,-1,GETDATE())
WHERE
invoicedate IS NULL
But when I execute it it keeps saying one row affected.
Can anyone please explain this to me?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You don't need to test for existing rows: the UPDATE will take care of everything with its WHERE clause.

If you want to find out which rows has been updated, use the OUTPUT clause. In that syntax, the DELETED.* means that it will output the values in the row before the update occured, which will show you that invoicedate is in fact NULL.

UPDATE [MPSales].[IT]

SET invoicedate = DATEADD(DAY,-1,GETDATE())

OUTPUT DELETED.*

WHERE invoicedate IS NULL

-- Gianluca Sartori

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 2

Thanks.

I still shows 1 row affected but the output does not return any results.

That is all I wanted to make sure off, I want to make it sure of one of my stored procedures, so that when that data is imported it can check all the records and update what is needed.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Any triggers on the table?

Are you sure the "1 row(s) affected" message is from this statement and not from other statements in the procedure?

-- Gianluca Sartori

djj SSCoach Points: 18831 More actions · Answer 4

djj

SSCoach

Points: 18831

May 6, 2015 at 9:21 am

#1795974

Do you have show execution plan on?

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 5

There are no triggers on the table, and I am sure it is from this statement, I was testing it first without putting it in a stored procedure.

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Weird. I don't know what to say.

DId you try to trace it with profiler (capture statement completed events and include rowcount column)?

-- Gianluca Sartori

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 7

The DB is on an Express edition so I can't use Profiler. Will do some research to look for alternatives.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

You can use profiler from a different machine.

-- Gianluca Sartori