CODEWITHSUNDEEP
sql-server-2008
MG SQL Muppet
MG SQL Muppet

Reputation: 127

SQL Update using a select query

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

  1. 156311 0 0
  2. 156310 0 0
  3. 156309 1 1
  4. 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

Upvotes: 1

Views: 225

Answers (1)

DatumPoint
DatumPoint

Reputation: 429

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
               )
;

Upvotes: 2

Related Questions