SQL Update using a select query

Question

I have a table where multiple records have the same LocationID. Some of these records have a 'true' value in the 'UsedinSA' field. I want to set all of the 'PartofContract' values to 'true' if there is one corresponding record with the same 'LocationID' and a 'true' value in 'UsedinSA'.

I've attempted as follows but this only updates the records that are already 'true'.

UPDATE tbAsset
SET tbAsset.PartofContract = 1
WHERE LocationID IN (SELECT dbo.tbAsset.LocationID FROM dbo.tbasset where tbAsset.UsedinSA = 1)

As an example of the data:

LocationID UsedinSA PartofContract

156311 0 0
156310 0 0
156309 1 1
156309 0 0

Record 4 should have 'PartofContract' set to 'True' because record 3 is and they have the same 'LocationID'.

Thanks in advance

Matt

DatumPoint · Accepted Answer · 2014-08-05 13:30:01Z

2

Try this - I only put the unique constraint in because I'm assuming your table has a PK or unique constraint:

use tempdb  --somewhere safe for a sandbox
--==========================================
drop    table dbo.tbAsset
;
--create some test data
select  1 as record_id --pk
        ,156311 as LocationID 
        ,0 as UsedinSA 
        ,0 as PartofContract
into    dbo.tbAsset
union   all
select  2 as record_id 
        ,156310 as LocationID 
        ,0 as UsedinSA 
        ,0 as PartofContract
union   all
select  3 as record_id
        ,156309 as LocationID 
        ,1 as UsedinSA 
        ,1 as PartofContract
union   all
select  4 as record_id
        ,156309 as LocationID 
        ,0 as UsedinSA 
        ,0 as PartofContract
;
create  unique clustered index ix_tbAsset_record_id on dbo.tbAsset(record_id)
;
--==========================================
with was_used_in_usa as 
        (
                SELECT ass.LocationID
                FROM dbo.tbAsset ass
                WHERE ass.UsedinSA = 1
        )
update  ass
set     ass.PartofContract = 1
from    dbo.tbAsset ass
where   exists ( select  * 
                 from    was_used_in_usa 
                 where   ass.LocationID = was_used_in_usa.LocationID
               )
;

answered Aug 5, 2014 at 13:30

DatumPoint

4314 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MG SQL Muppet Over a year ago

Works a treat. Really appreciate your time, effort and help.

Collectives™ on Stack Overflow

SQL Update using a select query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related