January 4, 2006 at 4:30 pm
Hi,
I have a quick 'update' question, if someone can provide some assistance that would be great because I am not figuring it out. What seem simple enough isn't getting me anywhere.
I have a table named JackInTheBox, I ran the following query on JackInTheBox to get the top 500 rows:
select top 500 * from dbo.JackInTheBox
where vendor = 'HOTEL' and overallStatusDesc in ('BREAKAGE 1','PARTBILL','RETURNED')
and lastChangeDt between '2005-02-01 00:00:00.00' and '2005-06-30 00:00:00.00' order by lastChangeDt asc
I got my 500 records but now I am asked to update those same 500 records, so I tried using:
update dbo.JackInTheBox
set LimitAmt=6000, WEXLimitAmt=6000, StatusDesc='AVAILABLE', overallStatusDesc='AVAILABLE',
lastChangeDt=getdate(), lastUserNm='TASIT500'
from
(select top 500 * from dbo.JackInTheBox
where vendor = 'HOTEL' and overallStatusDesc in ('BREAKAGE 1','PARTBILL','RETURNED')
and lastChangeDt between '2005-02-01 00:00:00.00' and '2005-06-30 00:00:00.00'
order by lastChangeDt asc) as t1
where JackInTheBox.vendor = t1.vendor
All it does is sit there with the following in the status bar: Executing query batch ...
Can someone tell me what I am doing wrong? This update is done on a single table with no join statement or relationships to another table(s).
Thanks
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 4, 2006 at 4:38 pm
You are joining on Vendor which is not the primary key/unique identifier of each row. This means you are updating more rows than you think, and you are updating each row 500 times due to the cartesian product generated.
This is the offending line of code:
where JackInTheBox.vendor = t1.vendor
Replace Vendor with the column or columns that uniquely identify each row.
January 4, 2006 at 4:43 pm
thanks for your response.
vendor is a column in the JackInTheBox table ... could you please expand further?
Thank you.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 4, 2006 at 4:59 pm
Does your table have a Primary Key ?
January 4, 2006 at 5:34 pm
Yes the table does have a PKID for column cardnbr. Other than that, that's it.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 4, 2006 at 5:36 pm
Then that's the column you should join on, instead of Vendor:
where JackInTheBox.cardnbr = t1.cardnbr
January 4, 2006 at 5:44 pm
hrmm not sure what happen to my other post but that was pretty damn easy. I spent over 2 hours trying to figure this darn update statement out.
I appreciate your prompt responses PW.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply