How to update based on the value of the source table

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

How to update based on the value of the source table

刘feng
hi dev:
 updating the CarbonData Table :
 UPDATE A SET (column_name1, column_name2,column_name3) =(select column_name1, column_name2,column_name3 from B   WHERE A.id = B.id  );

table  B is a mirror table of table A .
The fields in B are updated to table A
I want to do batch updates:
Whether to support updating based on the value of table B [column_name1, column_name2, column_name3] in B,
for example, when B.column_name1 == '' ,  no update operation is performed.
                      when B.column_name1 !='',  update operation is performed.
________________________________
neusoft TSD salute

---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication
is not the intended recipient,unauthorized use,forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: How to update based on the value of the source table

akashrn5
Hi,

I checked our test cases, we have a similar test case and works fine.
You can refer "update carbon table[select from source table with where and
exist]" in
UpdateCarbonTableTestCase.scala,

In that test case, you can have a query like below

*sql("""update iud.dest11 d set (d.c3, d.c5 ) = (select s.c33,s.c55 from
iud.source2 s where d.c1 = s.c11)""").show()*

It works fine. I didn't get the actual question, where you said

*when B.column_name1 == '' ,  no update operation is performed.
when B.column_name1 !='',  update operation is performed.*

can you please tell me what value are you trying to update?
If you can give the complete test case, it will be useful to check.

Also, please join the slack channel for detailed discussion.

https://join.slack.com/t/carbondataworkspace/shared_invite/zt-g8sv1g92-pr3GTvjrW5H9DVvNl6H2dg

Thanks,

Regards,
Akash R Nilugal



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to update based on the value of the source table

刘feng
hi,

table A:
id,column_name1, column_name2,column_name3
1,03OP20111684,,
2,01OP20111607,30,
3,01OP20111649,90,0020018393
4,01OP20111688,40,0020013010

table B ,:
id,column_name1, column_name2,column_name3
1,,10,
2,,40,
3,01OP20111638,,0020029804

For example: the first and second records only update column [column_name2], and the third record updates columns [column_name1,column_name3].
so ,I expect the updated result:
table A:
id,column_name1, column_name2,column_name3
1,03OP20111684,10,
2,01OP20111607,40,
3,01OP20111649,90,0020018393
4,01OP20111688,40,0020013010

Help me how to write the SQL for update ?

Thanks.
________________________________
neusoft TSD 刘feng


From: akashrn5<mailto:[hidden email]>
Date: 2020-10-09 12:56
To: [hidden email]<mailto:[hidden email]>
Subject: Re: How to update based on the value of the source table
Hi,

I checked our test cases, we have a similar test case and works fine.
You can refer "update carbon table[select from source table with where and
exist]" in
UpdateCarbonTableTestCase.scala,

In that test case, you can have a query like below

*sql("""update iud.dest11 d set (d.c3, d.c5 ) = (select s.c33,s.c55 from
iud.source2 s where d.c1 = s.c11)""").show()*

It works fine. I didn't get the actual question, where you said

*when B.column_name1 == '' ,  no update operation is performed.
when B.column_name1 !='',  update operation is performed.*

can you please tell me what value are you trying to update?
If you can give the complete test case, it will be useful to check.

Also, please join the slack channel for detailed discussion.

https://join.slack.com/t/carbondataworkspace/shared_invite/zt-g8sv1g92-pr3GTvjrW5H9DVvNl6H2dg

Thanks,

Regards,
Akash R Nilugal



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication
is not the intended recipient,unauthorized use,forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to update based on the value of the source table

akashrn5
Hi,

i got your question, we do not yet support the partial column update in
carbon.
When u say, set the column2, col3 by select col2, col3 from B where a.id =
b.id, then whenever the where condition is met, we select the whole column
from B and set to A.

So you can have query as
*update iud.a d set (d.col2) = (select s.col2 from iud.b s where d.id =
s.id)*

You will have output for your data as mentioned, like below after update

+---+----------------+-------------------+
|id   |col1                |col2 |col3           |
+---+----------------+-------------------+
|4    |01OP20111688|40  |0020013010|
|1    |03OP20111684|10  |                  |
|2    |01OP20111607|40  |                  |
|3    |01OP20111649|      |0020018393|
+---+------------+---------+-------------+

you will not have value 90 in the col2, as its value in table A, but you are
updating the value of table B's col2.


If you want the below data as you requested,

+---+------------+----+----------+
|id |col1        |col2|col3      |
+---+------------+----+----------+
|3  |01OP20111649|90  |0020018393|
|4  |01OP20111688|40  |0020013010|
|1  |03OP20111684|10  |          |
|2  |01OP20111607|40  |          |
+---+------------+----+----------+


Then you can write query like below

*update iud.a d set (d.col2) = (select s.col2 from iud.b s where d.id = s.id
and s.col2 != '')*


Regards,
Akash R Nilugal



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to update based on the value of the source table

刘feng

Thank you very much for solving my problems.

The table A may be a wide table.   100~200 colums.

Will this feature be supported in later versions?

Regards


---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication
is not the intended recipient,unauthorized use,forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to update based on the value of the source table

akashrn5
Hi,

we already support columns greater than 100, what exactly is your question?

Also, it would be helpful if you can ask and discuss issues in slack channel
than mailing list.
It would be easy to follow.

Thanks,

Akash R



--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to update based on the value of the source table

Zhangshunyu
In reply to this post by akashrn5
table A:
id,column_name1, column_name2,column_name3
1,03OP20111684,,
2,01OP20111607,30,
3,01OP20111649,90,0020018393
4,01OP20111688,40,0020013010

table B ,:
id,column_name1, column_name2,column_name3
1,,10,
2,,40,
3,01OP20111638,,0020029804

after update, the result of id=3 should be 3,01OP20111638,90,0020029804
all columns of B diff from A should be updated



-----
My English name is Sunday
--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
My English name is Sunday