Geek Answers Handbook

Exchange two rows of the database without violating the restrictions

I have a table regionkey:

areaid  -- primary key, int
region  -- char(4)
locale  -- char(4)

The rest of the database has a foreign key for areaid. This table has an index on (region, locale) with a unique constraint.

The problem is that I have two entries:

101   MICH   DETR
102   ILLI   CHIC

And I need to change the fields (region, locale) between them, so that I finish:

101   ILLI   CHIC
102   MICH   DETR

The naive approach will not work, as it violates the unique index for the region and locale:

update regionkey
     set region='ILLI', locale='CHIC' where areaid = 101; -- FAILS
update regionkey
     set region='MICH', locale='DETR' where areaid = 102;

How can i do this? Is there an atomic way to do a swap? Suggestions?

+5
source share
4 answers

SQL Server ( ),

update
    regionkey 
set
    region= CASE areaid WHEN 101 THEN 'ILLI' ELSE 'MICH' END, 
    locale= CASE areaid WHEN 101 THEN 'CHIC' ELSE 'DETR' END
where
    areaid IN (101, 102);

, ( )

update regionkey 
     set region='AAAA', locale='BBBB' where areaid = 101;
update regionkey 
     set region='MICH', locale='DETR' where areaid = 102;
update regionkey 
     set region='ILLI', locale='CHIC' where areaid = 101;

: ? , areaid

update
    regionkey 
set
    areaid = 203 - areaid 
where
    areaid IN (101, 102);
+8

, . , , , .

+1

?

, , , , .

0

, , ' , , , , :

UPDATE
    regionkey
SET
   region = '    ',
   locale = '    '
WHERE
    areaid in (101,102)

UPDATE
    regionkey
SET
    region = 'ILLI',
    locale = 'CHIC'
WHERE
    areaid = 101

UPDATE
    regionkey
SET
    region = 'MICH',
    locale = 'DETR'
WHERE
    areaid = 102

, , , , .

UPDATE: , UPDATE UNIQUE. UPDATE:

UPDATE
    regionkey
SET
    region = areaid,
    locale = areaid
WHERE
    areaid in (101,102)

, ( ) .

0

More articles:


All Articles