Stuck in SQL update
-
I have a table with the below data(orderNotes). ORDERID, NOTEID, TYPE_CD, NOTES
OrderIDNOTEID Type_CD txt_notes
18966 1829.0 9 Test Customer
18966 1837.0 9 Test Customer7
18966 1845.0 9 Test Customer2
18966 1853.0 9 Test Customer50
I want to update another table I call MASTER, using the ORDER_ID, update a field called OM_NOTES.
BUT I need the TYPE_CD = 9 and the LAST (or MAX) NOTEID
In other words I need the NOTES from the last record shown above
I tried
update master
set om_note = (select top 1 substring(txt_notes,1,60) from orderNotes order by noteid desc )
from master master, orderNotes nte
where nte.type_cd = '9'
and master.orderid = nte.orderid
Every record wound up with same notes in it. I know that is got to do with the top 1 in the select claus, but I am stuck on how to get past this.
Any help?
-
Hopefully this is a one-time occurrence. If not, you may choose to use a trigger to keep the information updated.
You might try
UPDATE master SET OM_NOTES =
( SELECT SUBSTRING( txt_notes, 1, 60 ) FROM orderNotes on1 (NOLOCK)
WHERE on1.OrderID = master.OrderID AND on1.Type_CD = '9' AND on1.NoteID = ( SELECT MAX(NoteID) FROM orderNotes onMax (NOLOCK) WHERE onMax.OrderID = master.OrderID AND on1.Type_CD = '9' ) )
I haven't set up tables to test this - so it may need to be tweaked. Hope this give you the general idea.
Guarddata-
Oops - forgot the Type.
Edited by - guarddata on 09/30/2003 08:54:49 AM
-
UPDATE Master SET Om_Note =
(SELECT TOP 1 SUBSTRING(Txt_Notes,1,60)
FROM OrderNotes
WHERE OrderID = Master.OrderID AND Type_Cd = 9
ORDER BY NoteID)
--Jonathan
Edited by - jonathan on 09/30/2003 09:02:21 AM
Edited by - jonathan on 09/30/2003 09:04:23 AM
--Jonathan -
GuardData: I am getting
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Jonathan: all the notes are the same notes with your solution
Any other ideas?
-
ghughes...it sounds like the NoteID can be duplicated for an OrderID. Is that correct? If so, my proposal will not work. Johnathans solution should work except that you need ORDER BY NoteID DESC. I just try to avoid using TOP and ORDER BY together because performance is not good (see BOL)
Guarddata
-
no, orderid is the common key between both tables. I need the txt_notes from the orderNotes table with the noteid is the max number with the orderid.
Edited by - ghughes on 09/30/2003 09:34:25 AM
-
quote:
GuardData: I am gettingSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Jonathan: all the notes are the same notes with your solution
Any other ideas?
As TOP 1 can only return one value, this doesn't make sense to me. Please post DDL and creation statements that duplicate the issue.
Thanks guarddata for noticing that missing DESC.
quote:
I just try to avoid using TOP and ORDER BY together because performance is not good...I would never use TOP without an ORDER BY clause. Otherwise you're relying on the clustered index, which is "hidden" data and subject to change. Actually, I avoid using the TOP modifier in production code, as it's not portable except to Sybase.
--Jonathan
--Jonathan -
Don't you really just want the note associated with max noteid?
select * from tbl_OrderNotes
go
--OrderIDNOTEIDType_CDtxt_notes
--189661829.09Test Customer
--189661837.09Test Customer7
--189661845.09Test Customer2
--189661853.09Test Customer50
select * from tbl_Master
go
--order_idom_note
--18966
update tbl_Master
set om_note = o.txt_notes
from tbl_OrderNotes o
where
o.OrderID = tbl_Master.Order_id
and
o.type_cd = 9
and
o.noteid in (select max(noteid) from tbl_OrderNotes)
go
select * from tbl_Master
go
--order_idom_note
--18966Test Customer50
J. Moseley
[font="Courier New"]ZenDada[/font]
-
ghughes - I realize that orderid is the common field. The real question is...Can there be duplicate NoteIDs? If not, my query should have worked. You may need to inspect the data. Try this:
SELECT OrderID, NoteID from OM_NOTES
GROUP BY OrderID, NoteID
HAVING COUNT(*) > 1
This will show the duplicates. If there are any, you will need to use the routine given by Jonathan.
Jonathan, you are right in the need for order when using TOP. The problem is that the full query runs in the background even after the TOP N rows are selected (see BOL). Not a problem in a small dataset. We often select against millions of queued entries and have found and index hint to be much more efficient.
Guarddata-
-
J Mosely, Thank you for simplifying my query. I wasn't thinking clearly
. Your update is much nicer than the one I created even though they do the same work.Guarddata-
-
quote:
Jonathan, you are right in the need for order when using TOP. The problem is that the full query runs in the background even after the TOP N rows are selected (see BOL). Not a problem in a small dataset. We often select against millions of queued entries and have found and index hint to be much more efficient.
Okay, now I'm intrigued. Even though we don't use TOP, the query engine sure does.
I just tried MAX and TOP with a 2 million row (wide) table that has an nonunique nonclustered index on the column I'm returning. The execution plans (Top) and STATISTICS IO (3 reads) are identical in performance, and each found and used the index with no hints.
The other reason we don't use TOP is that it is arbitrary unless you're ordering on a unique key. And arbitrary results can sneak up and kill you if you forget about their arbitrariness.
--Jonathan
--Jonathan -
I don't know how you tested TOP. Remember that we are talking about SELECT TOP N...ORDER BY. It may be able to use in index if the statistics are up-to-date. However, it is a quiet "gotcha" we try to avoid because if is just a big word
.Guarddata-
-
Oops - I screwed up on the last one! Tested it with two orderid's and discovered that what you really need is where exists, like this:
select * from tbl_OrderNotes
go
--OrderIDNOTEIDType_CDtxt_notes
--189661829.09Test Customer
--189661837.09Test Customer7
--189661845.09Test Customer2
--189661853.09Test Customer50
--111111854.09Test Customer11
--111111855.09Test Customer22
select * from tbl_Master
go
--order_idom_note
--18966NULL
--11111NULL
update tbl_Master
set om_note = o.txt_notes
from tbl_OrderNotes o
where exists
(
select OrderID, max(NOTEID)maxnoteid
from tbl_OrderNotes p
where p.noteid = o.noteid
and o.OrderID = tbl_Master.Order_id
group by OrderID
)
go
select * from tbl_Master
go
--order_idom_note
--18966Test Customer50
--11111Test Customer22
J. Moseley
[font="Courier New"]ZenDada[/font]
-
BTW, I know where exists and nested subqueries can be a bit confusing! The really simple solution is to create a temp table from an aggregate query on tbl_OrderNotes that has a one to one relationship with tbl_master; then update from that.
J. Moseley
[font="Courier New"]ZenDada[/font]
-
After a couple of hours I came up with this
UPDATE master SET om_note = SUBSTRING(nte.txt_notes,1,60)
from master master
inner join orderNotes nte on master.orderid = nte.orderid
where nte.type_cd = '9'
and nte.order_nte_id = (select MAX(order_nte_id) from orderNotes innertable with (NOLOCK)
where nte.orderid = innertable.orderid
and innertable.type_cd = '9')
I like your solution TnJMoseley ...seem easier to read. Which do you think is more efficent?
. Your update is much nicer than the one I created even though they do the same work.
.Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply