Update Data Extension by using SQL Query in Automation Studio of Marketing Cloud

Question

i am new to Marketing Cloud i am just learning it, i want to do the following things:

I am having 4 Data Extension De1, De2, De3, De4

De1 having 4 fields(fieldA, fieldB, fieldC, fieldD) De2 having 2 fields (fieldA, fieldB) De3 having 6 fields (fieldA, fieldB, fieldC, fieldD, fieldE, fieldF) De4 also having 6 fields that are in available in other (fieldA, fieldB, fieldC, fieldD, fieldE, fieldF)

All Data extension having 2 field in common,(fieldA, fieldB) fieldA - Primary Key

I want to update De4 with the values that are in the fields of De 1 to 3 using SQl Query in Automation Studio

See i want to update all 6 fields in de4 from all 3 data extension

Can someone let me know how to do it?

Answer 1

Ashish, I'm having a hard time understanding what you actually try to achieve. You say you want to achieve the same as a union statement, but the image you attached does not show that. In fact, I don't see the logic of how DE4 should be filled from that image.

I'm going to assume you made a mistake in your image of DE4 and want to get the rows of the 3 different DEs even though they don't have the same columns. What you need to do is use a UNION (ALL) statement, and create NULL columns for the columns that don't exist in a source DE.

Note: this assumes the columns in your DE4 are nullable!

SELECT
fieldA AS fieldA,
fieldB AS fieldB,
fieldC AS fieldC,
fieldD AS fieldD,
NULL AS fieldE,
NULL AS fieldF
FROM DE1

UNION ALL

SELECT
fieldA AS fieldA,
fieldB AS fieldB,
NULL AS fieldC,
NULL AS fieldD,
NULL AS fieldE,
NULL AS fieldF
FROM DE2

UNION ALL

SELECT
fieldA AS fieldA,
fieldB AS fieldB,
fieldC AS fieldC,
fieldD AS fieldD,
fieldE AS fieldE,
fieldF AS fieldF
FROM DE3

Note that 3rd party tools like DESelect exist that provide a drag and drop UI so you don't have to create this kind of hard queries manually.

Answer 2

I would use JOINs to connect these.

You would want to decide which values you want (see above chart for a great visualization of SQL Join results) to keep, and that will determine your type of join.

For instance, if you want to return all results from matching values in the tables, you would use:

SELECT a.FieldA, a.FieldB, b.FieldC, c.FieldD
FROM [DE1] a
INNER JOIN [DE2] b ON a.FieldA = b.FieldB
INNER JOIN [DE3] c ON a.FieldA = c.FieldA

Via the inner Join, you would be able to collect only those with matching FieldA on DE1 to DE2 and DE3.

There are many other options and different types of joins to meet your needs. If you provide more specifics on exactly what you are looking for, I can better specify my example code to fit your needs.

---

EDIT

This should solve your needs:

SELECT    a.FieldA
        , a.FieldB
        , CASE
            WHEN ISNULL(a.FieldC,'') <> '' THEN
                a.FieldC
            WHEN ISNULL(b.FieldC,'') <> '' THEN
                b.FieldC
            WHEN ISNULL(c.FieldC,'') <> '' THEN
                c.FieldC
            ELSE
                ''
        END as FieldC
        , CASE
            WHEN ISNULL(a.FieldD,'') <> '' THEN
                a.FieldD
            WHEN ISNULL(b.FieldD,'') <> '' THEN
                b.FieldD
            WHEN ISNULL(c.FieldD,'') <> '' THEN
                c.FieldD
            ELSE
                ''
        END as FieldD
        , CASE
            WHEN ISNULL(a.FieldE,'') <> '' THEN
                a.FieldE
            WHEN ISNULL(b.FieldE,'') <> '' THEN
                b.FieldE
            WHEN ISNULL(c.FieldE,'') <> '' THEN
                c.FieldE
            ELSE
                ''
        END as FieldE
        , CASE
            WHEN ISNULL(a.FieldF,'') <> '' THEN
                a.FieldF
            WHEN ISNULL(b.FieldF,'') <> '' THEN
                b.FieldF
            WHEN ISNULL(c.FieldF,'') <> '' THEN
                c.FieldF
            ELSE
                ''
        END as FieldF
FROM [DE1] a
FULL JOIN [DE2] b ON a.FieldA = b.FieldA AND a.FieldB = b.FieldB
FULL JOIN [DE3] c ON a.FieldA = c.FieldA AND a.FieldB = c.FieldB

It cascades the Field C-F values trying to fill it in first in DE1, then DE2 then DE3 - depending on if a value exists there or not.

Answer 3

You can try using a full outer union:

SELECT  fieldA, fieldB

FROM 
(
    (SELECT fieldA, fieldB FROM De1) 1
    FULL OUTER JOIN
    (SELECT fieldA, fieldB FROM De2) 2 on 1.fieldA=2.fieldA
    FULL OUTER JOIN 
    (SELECT fieldA, fieldB FROM De3) 3 on 1.fieldA=3.fieldA
)

the target should be De4 and assuming your primary ID is fildA