How do I update data from the user defined table type in sql
-1
Update details
set details.a=a, details.b=b
where Id=10 select a,b from @userdefinedtabletype
Here id is unique for all the records
sql-server asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
add a comment |
-1
Update details
set details.a=a, details.b=b
where Id=10 select a,b from @userdefinedtabletype
Here id is unique for all the records
sql-server asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
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 13 '18 at 11:31
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02
add a comment |
-1
-1
-1
Update details
set details.a=a, details.b=b
where Id=10 select a,b from @userdefinedtabletype
Here id is unique for all the records
sql-server asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
Update details
set details.a=a, details.b=b
where Id=10 select a,b from @userdefinedtabletype
Here id is unique for all the records
sql-server sql-server asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
edited Nov 13 '18 at 11:47
Devaraneni Laxman Rao
asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
asked Nov 13 '18 at 11:06
Devaraneni Laxman RaoDevaraneni Laxman Rao
293
293
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 13 '18 at 11:31
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02
add a comment |
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 13 '18 at 11:31
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02
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 13 '18 at 11:31
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 13 '18 at 11:31
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02
add a comment |
1 Answer
1
active
oldest
votes
0
Since you have not provided much detail as to what either table has in it I am going to assume that your user defined table only has one row in it. Otherwise you will get the last values that are returned from the query.
So I would use a CROSS APPLY to do the update such as:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
CROSS APPLY (select a,b from @userdefinedtabletype) t2
where t1.Id=10
If in fact these tables are related by an ID of some sort then you would probably want to do an INNER JOIN:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
INNER JOIN @userdefinedtabletype t2 on t1.id = t2.id
where t1.Id=10
answered Nov 13 '18 at 12:53
JM_JM_
62137
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
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%2f53279647%2fhow-do-i-update-data-from-the-user-defined-table-type-in-sql%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
Since you have not provided much detail as to what either table has in it I am going to assume that your user defined table only has one row in it. Otherwise you will get the last values that are returned from the query.
So I would use a CROSS APPLY to do the update such as:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
CROSS APPLY (select a,b from @userdefinedtabletype) t2
where t1.Id=10
If in fact these tables are related by an ID of some sort then you would probably want to do an INNER JOIN:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
INNER JOIN @userdefinedtabletype t2 on t1.id = t2.id
where t1.Id=10
answered Nov 13 '18 at 12:53
JM_JM_
62137
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
add a comment |
0
Since you have not provided much detail as to what either table has in it I am going to assume that your user defined table only has one row in it. Otherwise you will get the last values that are returned from the query.
So I would use a CROSS APPLY to do the update such as:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
CROSS APPLY (select a,b from @userdefinedtabletype) t2
where t1.Id=10
If in fact these tables are related by an ID of some sort then you would probably want to do an INNER JOIN:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
INNER JOIN @userdefinedtabletype t2 on t1.id = t2.id
where t1.Id=10
answered Nov 13 '18 at 12:53
JM_JM_
62137
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
add a comment |
0
0
0
Since you have not provided much detail as to what either table has in it I am going to assume that your user defined table only has one row in it. Otherwise you will get the last values that are returned from the query.
So I would use a CROSS APPLY to do the update such as:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
CROSS APPLY (select a,b from @userdefinedtabletype) t2
where t1.Id=10
If in fact these tables are related by an ID of some sort then you would probably want to do an INNER JOIN:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
INNER JOIN @userdefinedtabletype t2 on t1.id = t2.id
where t1.Id=10
answered Nov 13 '18 at 12:53
JM_JM_
62137
Since you have not provided much detail as to what either table has in it I am going to assume that your user defined table only has one row in it. Otherwise you will get the last values that are returned from the query.
So I would use a CROSS APPLY to do the update such as:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
CROSS APPLY (select a,b from @userdefinedtabletype) t2
where t1.Id=10
If in fact these tables are related by an ID of some sort then you would probably want to do an INNER JOIN:
Update details
set details.a=t2.a, details.b=t2.b
from details t1
INNER JOIN @userdefinedtabletype t2 on t1.id = t2.id
where t1.Id=10
answered Nov 13 '18 at 12:53
JM_JM_
62137
answered Nov 13 '18 at 12:53
JM_JM_
62137
answered Nov 13 '18 at 12:53
JM_JM_
62137
answered Nov 13 '18 at 12:53
JM_JM_
62137
62137
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
add a comment |
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Tq for ur reply
– Devaraneni Laxman Rao
Nov 13 '18 at 17:44
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
Actually my requirement is I inserted the two table s using datatable and using first table identity column is inserted into the second column id ( this id is same for all records) in that am using user defined table type for inserting the data at a time and also updating the second table some columns based on the id using the user defined table type ,but it's not working which I mentioned above query
– Devaraneni Laxman Rao
Nov 13 '18 at 17:53
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%2f53279647%2fhow-do-i-update-data-from-the-user-defined-table-type-in-sql%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
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 13 '18 at 11:31
You must explain more if do you want to solve your problem
– yusuf hayırsever
Nov 13 '18 at 11:39
I am updating the datatable using unique or reference id in ms SQL usind user defined table type
– Devaraneni Laxman Rao
Nov 13 '18 at 12:02