[RESOLVED] Whats wrong with this SQL
Update [image] set Active = 1 where
exists( select * from dwellingimage as d
right join [image] as i on
i.id = d.imageid
where imageid is null)
My Select statement above selects the correct number of records, but when I add the Update command it goes and update's the whole table. Can someone tell me how I should change it so that only those records in the select statement are updated.
# 1 Re: [RESOLVED] Whats wrong with this SQL
Update statement will update rows that satisfy the condition in the where clause.
since there are records in the inner select, this means that the condition is always true, hence all records will be updated.
the condition should be like this syntax (i don't understand the logic of the query):
Update [image]
set Active = 1
from dwellingimage as d
right join [image] as i on
i.id = d.imageid
where imageid is null
hspc at 2007-11-9 13:45:38 >
# 2 Re: [RESOLVED] Whats wrong with this SQL
basically, There are 2 tables that are joined by an Id. However, they are not totally dependent on each other. For an image to initially exist it has to be assigned to a dwelling record. Later down the line, if the dwelling record is deleted, the image doesn't, but instead should go to a status on inactive. the code was not written to work this way, though it has now and so I needed to perform a one off update against the status of those images that were no longer associated with a dwelling.
