-4

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?

0

3 Answers 3

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.

5

I would use JOINs to connect these.

enter image description here

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.

3
  • Thanks Gortonington, Will you please check image i added to describe what i actually want. Commented Jul 25, 2019 at 15:00
  • Thanks Once again Gortonington, You have invested your valuable time for writing such a large code, you have asked what i actually want, i just want to say that i want the result same that Union ALL does when there are same no of field in all De Commented Jul 26, 2019 at 6:37
  • @Ashish - what I created above will get you what you asked for - essentially faking unions with different field count DEs via full joins and case statements. I am unsure what you mean by your second comment, if you can clarify, I will update my answer accordingly Commented Jul 26, 2019 at 13:17
0

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

3
  • Thanks For your valuable time CodeRage But it will work in case when i am having only 2 fields in all dataextension See i want to update all 6 fields in de4 from all 3 data extenion that having diffrent no of fields and 2 are common So it will give error like: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. Commented Jul 25, 2019 at 13:44
  • 1
    look at using a join w3schools.com/sql/sql_join.asp
    – EazyE
    Commented Jul 25, 2019 at 13:46
  • Hi Ashish, I have updated my query
    – zuzannamj
    Commented Jul 25, 2019 at 13:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .