SQL not updating from join

Question

Im having an issue updating a table from a join. I did this kind of update many times but now its not working. Here is the original query:

select 
surveydatakey
,a.Strata as [surveydata strata]
,SurveyPeriod
,DateOfSurvey
,StationLocation 
,a.CardType
,a.timeperiod
,station_entrance
,Direction
,DayType
,EntranceType
,b.Strata as ActuaStrata 
,StartDate
,currentdate
from surveydata a
inner join MAP_Entrance_Population b
on a.station_entrance_id = b.station_entrance_id
and a.timeperiod = b.tp
and  a.strata <> b.strata
where mode = 'train'
and a.strata not in (1,14,15,21,22,23)
and dateofsurvey between startdate and  currentdate
order by a.strata

Now this is the update query:

begin tran
update a
set a.strata = b.strata
from surveydata a
inner join MAP_Entrance_Population b
on a.station_entrance_id = b.station_entrance_id
and a.timeperiod = b.tp
and  a.strata <> b.strata
where mode = 'train'
and a.strata not in (1,14,15,21,22,23)
and dateofsurvey between startdate and currentdate

The search query produces 218 results, the update says that it changes 218 results. In my search query I have the condition a.strata <> b.strata. My aim to to make these 2 equal each other. So I figured that after my update query, I should not get any results in my select query. But nothing actually changes. I still get the same 218 results after doing the update.

Any ideas?

Can you post an example of data? – veljasije Oct 01 '13 at 06:10 — veljasije, Oct 01 '13 at 06:10
I am using SQL Management Studio 2008 – user2343837 Oct 01 '13 at 06:11 — user2343837, Oct 01 '13 at 06:11
@veljasije The columns I am trying to update are just int. – user2343837 Oct 01 '13 at 06:13 — user2343837, Oct 01 '13 at 06:13

user172839 · Answer 1 · 2013-10-01T06:21:08.430

0

Could it because there isn't any records from Table B to be joined to Table A based on your join condition?

Try running the following query

select *
 from surveydata a
where mode = 'train'
and a.strata not in (1,14,15,21,22,23)
and dateofsurvey between startdate and currentdate 
and not exists
(
     SELECT 1
from MAP_Entrance_Population b
WHERE a.station_entrance_id = b.station_entrance_id
and a.timeperiod = b.tp
and  a.strata <> b.strata
 )

edited Oct 01 '13 at 06:21

answered Oct 01 '13 at 06:13

user172839

1,035
1
10
19

But they shouldn't I be getting no results from my select query? I am getting 218 results. – user2343837 Oct 01 '13 at 06:14
1

I noticed you have a BEGIN TRANSACTION in your statement. Just confirming whether you have committed this transaction? – user172839 Oct 01 '13 at 06:17
In the query Im assuming that Im keeping the same ON clause as before. Im running it, but nothing is happening, it just keeps on running. (Its a very small dataset) – user2343837 Oct 01 '13 at 06:18
yes I have, but it should work anyway within the same connection. – user2343837 Oct 01 '13 at 06:19

score 0 · Answer 2 · answered Oct 01 '13 at 06:23

Try the following two queries

select 
.
.
.
from surveydata a
inner join MAP_Entrance_Population b
on a.station_entrance_id = b.station_entrance_id
and a.timeperiod = b.tp
where a.strata <> b.strata
and mode = 'train'
and a.strata not in (1,14,15,21,22,23)
and dateofsurvey between startdate and  currentdate
order by a.strata

Update

update a
set a.strata = b.strata
from surveydata a
inner join MAP_Entrance_Population b
on a.station_entrance_id = b.station_entrance_id
and a.timeperiod = b.tp
where a.strata <> b.strata
and mode = 'train'
and a.strata not in (1,14,15,21,22,23)
and dateofsurvey between startdate and currentdate

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

What you describe could happen if 218 records in MAP_Entrance_Population have strata set to NULL. In that case, your select query will bring rows back, as NULL is not equal to NULL or anything else, and your update query will update surveydata to have strata set to NULL, which is still not equal to NULL.

When you find surprising things you can't quite figure out happening with the comparison operators in SQL, often the underlying cause is that you're comparing something with NULL.

edited May 23 '17 at 10:25

Community

1
1

answered Oct 01 '13 at 06:25

Matt Gibson

37,886
9
99
128

I think you are right in terms of comparing to Null. I think I am doing that. Will need to look deeper into the query. Thank you – user2343837 Oct 01 '13 at 06:46

SQL not updating from join

3 Answers3