how to split one value from sql database into two rows
-2
how to split one value from sql database and devide into two rows (one row set 1000000, one row is the balance) Others less than 1000000.or any idea using coldfusion . Thanks in advance.
Example
No. Code Name Account No Total
1 123 black 123456789 160000.00
2 124 red 111111222 5200.00
3 124 blue 444555666 121000.00
I want This result
No. Code Name Account No Total
1 123 black 123456789 100000.00
2 123 black 123456789 60000.00
3 124 red 111111222 5200.00
4 124 blue 444555666 100000.00
5 124 blue 444555666 21000.00
sql
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
add a comment |
-2
how to split one value from sql database and devide into two rows (one row set 1000000, one row is the balance) Others less than 1000000.or any idea using coldfusion . Thanks in advance.
Example
No. Code Name Account No Total
1 123 black 123456789 160000.00
2 124 red 111111222 5200.00
3 124 blue 444555666 121000.00
I want This result
No. Code Name Account No Total
1 123 black 123456789 100000.00
2 123 black 123456789 60000.00
3 124 red 111111222 5200.00
4 124 blue 444555666 100000.00
5 124 blue 444555666 21000.00
sql
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
1
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
Can you give an example what happen if the total >1000000
– Daniel Tran
Nov 15 '18 at 2:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 15 '18 at 7:00
add a comment |
-2
-2
-2
how to split one value from sql database and devide into two rows (one row set 1000000, one row is the balance) Others less than 1000000.or any idea using coldfusion . Thanks in advance.
Example
No. Code Name Account No Total
1 123 black 123456789 160000.00
2 124 red 111111222 5200.00
3 124 blue 444555666 121000.00
I want This result
No. Code Name Account No Total
1 123 black 123456789 100000.00
2 123 black 123456789 60000.00
3 124 red 111111222 5200.00
4 124 blue 444555666 100000.00
5 124 blue 444555666 21000.00
sql
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
how to split one value from sql database and devide into two rows (one row set 1000000, one row is the balance) Others less than 1000000.or any idea using coldfusion . Thanks in advance.
Example
No. Code Name Account No Total
1 123 black 123456789 160000.00
2 124 red 111111222 5200.00
3 124 blue 444555666 121000.00
I want This result
No. Code Name Account No Total
1 123 black 123456789 100000.00
2 123 black 123456789 60000.00
3 124 red 111111222 5200.00
4 124 blue 444555666 100000.00
5 124 blue 444555666 21000.00
sql
sql
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
edited Nov 15 '18 at 6:47
Purplish P
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
asked Nov 15 '18 at 2:02
Purplish PPurplish P
12
12
1
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
Can you give an example what happen if the total >1000000
– Daniel Tran
Nov 15 '18 at 2:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 15 '18 at 7:00
add a comment |
1
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
Can you give an example what happen if the total >1000000
– Daniel Tran
Nov 15 '18 at 2:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 15 '18 at 7:00
1
1
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
Can you give an example what happen if the total >
1000000– Daniel Tran
Nov 15 '18 at 2:08
Can you give an example what happen if the total >
1000000– Daniel Tran
Nov 15 '18 at 2:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql, oracle, sql-server, db2, ...– a_horse_with_no_name
Nov 15 '18 at 7:00
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql, oracle, sql-server, db2, ...– a_horse_with_no_name
Nov 15 '18 at 7:00
add a comment |
1 Answer
1
active
oldest
votes
0
First you need to insert a new row:
INSERT INTO TABLE (CODE, NAME, ACCOUNT_NO, TOTAL)
SELECT
CODE,
NAME,
ACCOUNT_NO,
TOTAL MOD 100000 AS NEW_TOTAL
FROM TABLE
WHERE TOTAL > 100000;
That command will insert the second row. Now you need to fix the first row.
UPDATE TABLE
SET TOTAL = TOTAL - (TOTAL MOD 100000)
WHERE TOTAL > 100000;
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53311384%2fhow-to-split-one-value-from-sql-database-into-two-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
First you need to insert a new row:
INSERT INTO TABLE (CODE, NAME, ACCOUNT_NO, TOTAL)
SELECT
CODE,
NAME,
ACCOUNT_NO,
TOTAL MOD 100000 AS NEW_TOTAL
FROM TABLE
WHERE TOTAL > 100000;
That command will insert the second row. Now you need to fix the first row.
UPDATE TABLE
SET TOTAL = TOTAL - (TOTAL MOD 100000)
WHERE TOTAL > 100000;
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
add a comment |
0
First you need to insert a new row:
INSERT INTO TABLE (CODE, NAME, ACCOUNT_NO, TOTAL)
SELECT
CODE,
NAME,
ACCOUNT_NO,
TOTAL MOD 100000 AS NEW_TOTAL
FROM TABLE
WHERE TOTAL > 100000;
That command will insert the second row. Now you need to fix the first row.
UPDATE TABLE
SET TOTAL = TOTAL - (TOTAL MOD 100000)
WHERE TOTAL > 100000;
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
add a comment |
0
0
0
First you need to insert a new row:
INSERT INTO TABLE (CODE, NAME, ACCOUNT_NO, TOTAL)
SELECT
CODE,
NAME,
ACCOUNT_NO,
TOTAL MOD 100000 AS NEW_TOTAL
FROM TABLE
WHERE TOTAL > 100000;
That command will insert the second row. Now you need to fix the first row.
UPDATE TABLE
SET TOTAL = TOTAL - (TOTAL MOD 100000)
WHERE TOTAL > 100000;
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
First you need to insert a new row:
INSERT INTO TABLE (CODE, NAME, ACCOUNT_NO, TOTAL)
SELECT
CODE,
NAME,
ACCOUNT_NO,
TOTAL MOD 100000 AS NEW_TOTAL
FROM TABLE
WHERE TOTAL > 100000;
That command will insert the second row. Now you need to fix the first row.
UPDATE TABLE
SET TOTAL = TOTAL - (TOTAL MOD 100000)
WHERE TOTAL > 100000;
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
edited Nov 15 '18 at 7:01
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
answered Nov 15 '18 at 2:13
Daniel TranDaniel Tran
4,141520
4,141520
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53311384%2fhow-to-split-one-value-from-sql-database-into-two-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Tag your question with the database you are using.
– Gordon Linoff
Nov 15 '18 at 2:04
I'm lost. Both 260,000 and 400,000 are less than 1,000,000, so I have not idea why they are split.
– Gordon Linoff
Nov 15 '18 at 2:05
Can you give an example what happen if the total >
1000000– Daniel Tran
Nov 15 '18 at 2:08
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql,oracle,sql-server,db2, ...– a_horse_with_no_name
Nov 15 '18 at 7:00