Geek asks and answers

Join tables with overrides

I have 2 tables - current and proposed - as follows:

ACTUAL
    Id|desc|Allocation|changeBit
    1|X|20||
    2|y|30||

PROPOSED
        Id|desc|Allocation|changeBit
        1|X|30|U|
        3|z|40|I|

The desired result is as follows:

    1|X|30|U|
    2|y|30||
    3|z|40|I|

bit A 'U' causes the entry in the "proposed" to override the "actual". The "I" in the proposed simply indicates that a "new" entry should be added.

What would be the most elegant way to achieve this?

Ideally, I would like to avoid creating temporary tables with subsequent insert-update.
I am using sql server 2008.

+3
source share
3 answers

union all not exists. , coalesce() isnull() full outer join .

select Id, desc, Allocation, changeBit
from proposed
union all
select Id, desc, Allocation, changeBit
from actual a
where not exists (select 1 from proposed p where a.id = p.id);

EDIT:

, :

select Id, desc, Allocation, changeBit
from proposed
where changebit in ('U', 'I')
union all
select Id, desc, Allocation, changeBit
from actual a
where not exists (select 1 from proposed p where a.id = p.id);

"U" "I" ( ). , , , , .

+1

full outer join, coalesce, , :

SELECT          COALESCE (p.id, a.id) AS id, 
                COALESCE (p.desc, a.desc) AS desc,
                COALESCE (p.allocation, a.allocation) AS allocation,
                COALESCE (p.changebit, a.changebit) AS changebit
FROM            actual a
FULL OUTER JOIN proposed p ON a.id = p.id
+1

This is a bit off-base, but judging by your last statement, it looks like you are using this query to create the INSERTand operator UPDATE. Since you are using SQL-Server 2008, have you thought of using MERGEall of this in one command instead of three (query, insert, update)?

Assuming it PROPOSEDcontains your changes, you can do it like this:

MERGE ACTUAL AS A
USING PROPOSED AS P
    ON A.Id = P.Id
WHEN MATCHED
    THEN UPDATE
    SET A.Allocation = P.Allocation
WHEN NOT MATCHED
    THEN INSERT (Id, [Desc], Allocation)
    VALUES (Id, [Desc], Allocation);

Using this, you can flush the column changeBitsince the statement MERGEtells you whether INSERTor UPDATE.

SQLFiddle

0
source

More articles:


All Articles