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. --------------------------------------------------------------------------------------------------- |
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/ |
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. --------------------------------------------------------------------------------------------------- |
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/ |
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. --------------------------------------------------------------------------------------------------- |
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/ |
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
|
Free forum by Nabble | Edit this page |