September 3, 2008 at 10:49 am
Hi all,
I need to update a field with the value from another table.
1st Table NIT has fields, cno and desc
2nd Table CIT has fields, cno and itm
Below are the values in CIT and NIT.
Table NIT
CNO DESC
100temp
100temp2
1012455
1012466
.
.
.
.
Table CIT
CNOITM
1000163
100PL01
101MEE12
101WEK11
.
.
.
I need to add new field 'desc' to CIT table and populate the CIT.desc from NIT.desc based on the cno.
But the trick is, I need to populate the value of first cno in the CIT.desc. FOr example,
for CIT.cno=100, I need to populate CIT.desc with 'temp' from NIT.desc and not temp2.
for CIT.cno=101, I need to populate CIT.desc with '2455' from NIT.desc and not 2466.
I am not sure how I can do this. I tried below but no success.
update cit c1 set (c1.desc) = (select n1.desc from nit n1 where c1.cno=n1.cno);
Any help will be appreciated.Thank you
September 3, 2008 at 11:23 am
psangeetha (9/3/2008)
But the trick is, I need to populate the value of first cno in the CIT.desc.
Define "first". Remember that relational database have no inherent order of their own, only what you define.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:39 am
Rbarry,
I meant the CIT.descr should be populated with NIT.descr from the first inserted record in NIT.
Below is the test case:
SQL> select * from nit;
CNO DESCR
---------- ------------------------------
100 temp
100 temp2
101 2455
101 2466
101 2242
101 edil
6 rows selected.
SQL> select * from cit;
CNO ITM
---------- ------------------------------
100 0163
100 PL01
101 MEE12
101 WEK11
After I add descr field to CIT, the result of the update to CIT.descr should be like:
select * from cit;
CNO ITMDESCR
---------- ------
100 0163temp
100 PL01temp
101 MEE122455
101 WEK112455
Thank you for all the help.
September 3, 2008 at 11:51 am
And how can you tell which one was the first inserted? SQL Server does not necessarily either store them or return them in the same order that you inserted them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:56 am
Oh Okay. I thought the select * from table, will always retrieve the rows in the same order. I just tried a select on few tables and it results with the rows in the same order. I am not sure if I am missing something here..
Is it possible to get the value for the update from the select statement??? Like, select n1.descr from nit n1,cit c1 where c1.cno=n1.cno. And get the n1.descr from the first record and update in CIT table?? Is this possible???
Thanks for your help
September 3, 2008 at 12:10 pm
psangeetha (9/3/2008)
Oh Okay. I thought the select * from table, will always retrieve the rows in the same order. I just tried a select on few tables and it results with the rows in the same order. I am not sure if I am missing something here..
Yes, this generally works for small tables if there is no clustered index, however, even then it is not guaranteed. Larger tables have an ever increasing chance that the rows will be in a different order. If there is (or ever was) a clustered index on the table, then they will always be in the order of the index. And don't get me started on partitioned tables.
The point is, if you do not define an order, then you cannot guarantee that you can get rows back in that order.
Is it possible to get the value for the update from the select statement??? Like, select n1.descr from nit n1,cit c1 where c1.cno=n1.cno. And get the n1.descr from the first record and update in CIT table?? Is this possible???
Yes, there are ways to do that, but without an "Order By" or a clustered index somewhere, you cannot be sure which of the multiple matching rows will be seen as the "first".
Again, for small tables, it will usually come back the way that you put them in, but you cannot be sure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:17 pm
Rbarry,
Thanks for your comments. It is considerably small table and this will be a one time update statement for the existing records. I think it should be fine.
Do you know how to get the value from the select statement returning multiple rows??
For example, I need to get the value of descr from the below select statement (value of the n1.descr from the 1st returned row of matching c1.cno=n1.cno.
update cit c1 set (c1.descr) = (select n1.descr from nit n1 where c1.cno=n1.cno);
Thanks again
September 3, 2008 at 12:53 pm
This should work then:
Update c1 set descr = n1.descr
From cit c1
Join nit n1 ON c1.cno=n1.cno
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:58 pm
Rbarry, This worked.
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!
I appreciate it!
September 3, 2008 at 1:52 pm
Glad I could help.
Just remember in the future though: If you want to be able to retrieve rows in the order that they were inserted, you will need some explicit way to keep track of that. A "CreatedDate" column with a default "= getdate()" is probably the easiest, if there is not already an identity column.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:27 pm
rbarryyoung (9/3/2008)
This should work then:
Update c1 set descr = n1.descr
From cit c1
Join nit n1 ON c1.cno=n1.cno
actually, that join would set descr = to the "2nd" description that occurs, not the first.
In order to establish an order for them, insert the cno and descr columns into a temp table with an identity column (ID). The min(ID) for each cno is the "first".
But who's counting anyway.
The probability of survival is inversely proportional to the angle of arrival.
September 3, 2008 at 3:31 pm
sturner (9/3/2008)
rbarryyoung (9/3/2008)
This should work then:
Update c1 set descr = n1.descr
From cit c1
Join nit n1 ON c1.cno=n1.cno
actually, that join would set descr = to the "2nd" description that occurs, not the first.
Nope. Not on my system, anyway, it set it to the first.
But then that is the indeterminate nature of this approach.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:34 pm
how did you know it was the "first"?
The probability of survival is inversely proportional to the angle of arrival.
September 3, 2008 at 3:36 pm
Because I put it in.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:45 pm
Here is the script that I used to test it:
Set Nocount ON
create table cit(cno int, descr varchar(16))
create table nit(cno int, descr varchar(16))
go
Insert into cit select 100, ''
Insert into nit select 100, 'first row'
go
Insert into nit select 100, 'Another row'
go
Insert into nit select 100, 'Third row'
go
Select * from cit
Select * from nit
-- ====
Update c1 set descr = n1.descr
From cit c1
Join nit n1 ON c1.cno=n1.cno
Select * from cit
GO
drop table cit
drop table nit
go
And here are the results I get:
cno descr
----------- ----------------
100
cno descr
----------- ----------------
100 first row
100 Another row
100 Third row
cno descr
----------- ----------------
100 first row
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply