SQL statement doesn't update any rows

Question

EDIT: Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry!

I don't know what is wrong with this statement, but it will not UPDATE any row.

IF EXISTS (
  SELECT * 
  FROM dtRaces 
  WHERE 
      Date = @date 
  AND Location = @location 
  AND Time = @time
  ) 
  UPDATE dtRaces SET 
    Date=@date, 
    Location=@location, 
    Time=@time
    City=@city, 
    State=@state, 
    Title=@title
  WHERE 
      Date = @date 
  AND Location = @location 
  AND Time = @time 
ELSE 
  INSERT INTO dtRaces (Date, Location, Time, City, State, Title) 
  VALUES (@date, @location, @time, @city, @state, @title)

Any ideas? I'm stumped. This is with SQL Server 2008, but I have not learned how to use MERGE yet.

"If rows exist with these values, update those rows to have the exact same values." You don't see the problem with that? — Anthony Grist, Feb 24 '12 at 11:19
@AnthonyGrist - Yeah, not entirely sure what the OP is trying to do here. Maybe the UPDATE should update the City, State and Title fields as well, or instead? — MatBailie, Feb 24 '12 at 11:25
I'm sorry, my original post was edited and improperly changed. It's now fixed to include other values in the UPDATE statement. — user1230593, Feb 24 '12 at 20:25

score 3 · Answer 1 · answered Feb 24 '12 at 11:21

3

You've got the statement:

 UPDATE dtRaces SET 
    Date=@date, 
    Location=@location, 
    Time=@time 
  WHERE 
      Date = @date 
  AND Location = @location 
  AND Time = @time

This is updating columns to have values, but only where they already have those values - you'll never see updated data from this.

answered Feb 24 '12 at 11:21

Jon Egerton

40,401
11
97
129

I'm sorry, my original post was edited and improperly changed. It's now fixed to include other values in the UPDATE statement. – user1230593 Feb 24 '12 at 20:23
EDIT: Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry! – user1230593 Feb 24 '12 at 20:45
@user1230593: Yes it does have to return true. Having said that the `EXISTS` in your example code should have been ok. – Jon Egerton Feb 24 '12 at 22:17

score 1 · Answer 2 · edited Feb 24 '12 at 20:39

1

I suggest you compare the values.

Start with

IF EXISTS (SELECT * FROM dtRaces WHERE Date = @date AND Location = @location AND Time = @time) 
begin
print('exists')
end
else 
begin
print('insert')
end

You could change the print statements to select the information and compare it.

edited Feb 24 '12 at 20:39

Oleg Dok

21,109
4
45
54

answered Feb 24 '12 at 11:21

Johan

753
2
11
31

@OlegDok - The answer does not say that they are logically different, only that there is a very slight performance difference. As far as I recall it may actually be true, for reasons deep inside the engine, there is much discussion on it that can be found on the web. But it is so intangible that it makes *virtually* no *perceptible* difference. I have no idea how you justify a -1 for that. Especially considering it may be technically true. – MatBailie Feb 24 '12 at 11:28
I removed that statement about select 1 instead of select * since it seams controversial and it was several years ago since I last tested it. – Johan Feb 24 '12 at 11:29
@Dems - As far as I know - Sql Server does not consider columns list at all inside EXISTS clause. So - there will no any even tiny and technical performance difference. You can check it by denying access to one of columns to specific user and let this user use SELECT * - there will be no security error, instead if you do simple select * - there will be one\ – Oleg Dok Feb 24 '12 at 11:31
@OlegDok - If you are so convinced that you ***know*** the answer, read Martin Smith's answer here http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists . An answer that quotes one of the SQL Server team (it makes an extremely minor difference in compilation), and still concludes that no conclusion can be drawn. In short - It appears that you think you know something categorically, but that you simply can't know it without de-compiling and analyisng SQL Server. Sorry. – MatBailie Feb 24 '12 at 11:36
@Dems Agree - all the things connected to Query Optimizer is a black box and probably highest secret and "know hows". But if this mean that we cannot say something about - no. I read the noticed questions and answer. I understand that in compiletime there can be some difference - but during my tests - there is no such a difference. Even is MS guy says that it is. "Do yourself a favor - trust no one, even do not trust to MS tech staff, test it" (c) They too can be wrong. But even if **I am wrong** with this - the gain will be so negligible (even under the borders of errors in measurements)... – Oleg Dok Feb 24 '12 at 11:55
@Dems ... that it is completely looks like "permature optimization". And sorry for my english. – Oleg Dok Feb 24 '12 at 11:56
I'm sorry, my original post was edited and improperly changed. It's now fixed to include other values in the UPDATE statement. – user1230593 Feb 24 '12 at 20:23
EDIT: Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry! – user1230593 Feb 24 '12 at 20:45

Oleg Dok · Answer 3 · 2012-02-24T20:47:12.450

You are updating the row to the same values if the row is exists. So - it looks like nothing happened.

But if you create trigger after update - you definitely will see that it runs

PS: Overall behavior is little bit strange.

Edit:

Now it looks better, but you still have no needs to update some of values to the same values, see this:

IF EXISTS (
  SELECT * 
  FROM dtRaces 
  WHERE 
      Date = @date 
  AND Location = @location 
  AND Time = @time
  ) 
  UPDATE dtRaces SET 
    City=@city, 
    State=@state, 
    Title=@title
  WHERE 
      Date = @date 
  AND Location = @location 
  AND Time = @time 
ELSE 
  INSERT INTO dtRaces (Date, Location, Time, City, State, Title) 
  VALUES (@date, @location, @time, @city, @state, @title)

For using MERGE you have to guarrantee, that these three columns will always be unique: Date, Time, Location. If so - the MERGE will look like this:

MERGE dtRaces T
USING (SELECT 1 S) S 
ON   T.Date = @date AND T.Location = @location AND Time = @time 
WHEN MATCHED THEN UPDATE SET 
    City=@city, 
    State=@state, 
    Title=@title
WHEN NOT MATCHED THEN
    INSERT (Date, Location, Time, City, State, Title) 
    VALUES (@date, @location, @time, @city, @state, @title);

PS: If one of your variables (@Date, @Time, @Location) has the value of NULL - you also will never get an update.

I'm sorry, my original post was edited and improperly changed. It's now fixed to include other values in the UPDATE statement. — user1230593, Feb 24 '12 at 20:24
EDIT: Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry! — user1230593, Feb 24 '12 at 20:45

score 0 · Answer 4 · edited Feb 24 '12 at 16:06

0

UPDATE dtRaces SET 
  Date=@date, 
  Location=@location, 
  Time=@time 
WHERE 
  Date = @date 
  AND Location = @location 
  AND Time = @time

You are trying to update the values with @date, @location, @time and you are trying to find the records with date=@date,location=@location,time=@time.

edited Feb 24 '12 at 16:06

Michael Fredrickson

36,839
5
92
109

answered Feb 24 '12 at 15:57

Virus

3,215
7
29
46

EDIT: Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry! – user1230593 Feb 24 '12 at 20:45

score 0 · Answer 5 · answered Feb 24 '12 at 20:46

0

Ok I figured it out, I have an if statement that must return true in order for the SQL to run, that's the problem. Nothing was wrong with my SQL. Sorry!

answered Feb 24 '12 at 20:46

user1230593

243
1
3
9

SQL statement doesn't update any rows

5 Answers5