CODEWITHSUNDEEP
.netsql-server
user1230593
user1230593

Reputation: 243

SQL statement doesn't update any rows

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.

Upvotes: 1

Views: 143

Answers (5)

Oleg Dok
Oleg Dok

Reputation: 21766

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.

Upvotes: 0

user1230593
user1230593

Reputation: 243

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!

Upvotes: 0

Johan
Johan

Reputation: 763

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.

Upvotes: 1

Virus
Virus

Reputation: 3415

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.

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

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.

Upvotes: 3

Related Questions