Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
we have 2 tables:
tableNewNumbers:
number | state |
---|---|
123 | active |
234 | active |
tableOldNumbers:
number | state |
---|---|
234 | deleted |
888 | active |
The state of all numbers in "tableOldNumbers" which do not exist in "tableNewNumbers" should be changed to 'removed'.
Our result table should be:
number | state |
---|---|
123 | active |
234 | active |
888 | removed |
Data:
LOAD
Number
,State
FROM Table1....;
CONCATENATE (Data)
LOAD
Number
,'removed' AS State
FROM Table2
WHERE NOT EXISTS(Number);
Hope this helps,
Jason
Data:
LOAD
Number
,State
FROM Table1....;
CONCATENATE (Data)
LOAD
Number
,'removed' AS State
FROM Table2
WHERE NOT EXISTS(Number);
Hope this helps,
Jason
Thanks for your quick answer.
This was also our idea, but the field "state" has already a value in Table2 which has to be updated.
The result of your advice is:
number | state |
---|---|
123 | active |
234 | active |
234 | deleted |
888 | active |
Hi Sleepa,
what Jason Michaelides suggested is working fine .
Kindly find the attached application.
Regards,
Sampath Kumar G
Are you sure you are loading tableNewNumbers first? To use your exact table names in my script:
Data:
LOAD
Number
,State
FROM tableNewNumbers....;
CONCATENATE (Data)
LOAD
Number
,'removed' AS State
FROM tableOldNumbers
WHERE NOT EXISTS(Number);
If 234 existis already in tableNewNumbers it will not be loaded from tableOldNumbers. Likewise, if 888 does not exist in tableNewNumbers then it will be loaded from tableOldNumbers.
Jason
Slight modification. If in the real world you are trying to find out how to join two tables because you are pulling in some different fields then you can do this. Otherwise, if the tables are the same then Jason's way is fine.
Data:
LOAD
Number
,State
FROM Table1....;
LEFT JOIN LOAD
Number
,State
,SomeOtherField
FROM Table2;
CONCATENATE (Data)
LOAD
Number
,'removed' AS State
,SomeOtherField
FROM Table2
WHERE NOT EXISTS(Number);
Seems pretty strange wanting to join on state though, maybe this isn't what you're really trying to do.
Hi Sleepa,
What Jason and Sampath said is right. I also tried that and i got the perfect output as per yours. Check it out once again.
Regards,
Arun Prasadh.N.
THANKS!!!
Our problem was that we got a different result when Table2 was already loaded.
Table2:
LOAD Number,
State
From Table2;
Data:
LOAD Number,
State
FROM Table1;
CONCATENATE
LOAD Number,
'removed' as State
RESIDENT Table2
WHERE NOT EXISTS(Number);
Now we concatenate while loading Table2 the first time. Then it is correct.
Yes - EXISTS checks back for that field in any previous section of the script.