Solved

sql update X number of records

Posted on 2012-04-12
29
325 Views
Last Modified: 2012-04-30
Hi,
I have an sql server database.  I want to update an item's  status code in one table if the item exists in another table. My problem is that the item can have multiple instances in either table and I only want to update as many items in one table as exist in the other table.  I'm using the update statements below but they update everything that matches right now.

update a
set a.stat_cd = 'X'
from t_issues a inner join t_order b on a.agin = b.agin
where a.mrg_nbr = b.mrg_nbr
and b.mrg_nbr = '1925225'
and b.scanned = 'N'
and (a.stat_cd = 'V')

update a
set a.scanned = 'Y', a.in_seq = 'N'
from t_order a inner join t_issues b on a.agin = b.agin
where a.mrg_nbr = b.mrg_nbr
and b.mrg_nbr = '1925225'
and a.scanned = 'N'
and b.stat_cd = 'X'
0
Comment
Question by:coperations07
  • 16
  • 13
29 Comments
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
What are you using as the tie breaker?  I mean to say, if there are 100 instances in one table and 1000 in the other table what determines which 100 rows of the 1000 are to get updated?
0
 

Author Comment

by:coperations07
Comment Utility
It doesn't matter to me which ones are updated.  There's an ID field that is unique, so I could go by the highest ID's in order.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
And there is no relationship between that second key at all and the oposite table?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I think I would manipulate the rowcount.  This works like TOP on update and inserts.
Setting it back to zero goes back to normal.


Compute the number of matching rows

declare @count int
set @count =   ( number of matching rows)
set rowcount @count
.... Your Update statement .....

set @count = 0  (make sure you reset rowcount afterwards or the limit will still be in effect)
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I do have to state , however, that I still think there should be some intelligence applied to which rows are select.
0
 

Author Comment

by:coperations07
Comment Utility
That's correct, there's no relation between the table keys.  I'll give your suggestion a try and see if it will work for me.

Thanks!
Dave
0
 

Author Comment

by:coperations07
Comment Utility
I've been trying to work with rowcount some, but I don't have what I need yet.  If there are multiple agins (item numbers) to update can I use rowcount without having to put it in a cursor?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
Is this something like what you are doing within a curson to process through all the items?

if you set rowcount before a statement like.

update tablexxx  set fielda = 'xyz'

it should still only update the number of rows limited by the rowcount you set.

You just use the joint to select the count(*) of the number of cross matches

          select @mylimitingcount= count(*) from .....

then

set rowcount = @mylimitingcount

then

update table where ....

set rowcount = 0

===============================================================
and you want to do this in a single statement without having it in a cursor to process to process the individual items?

I can't think of a way immediately because of how rowcount works.

================================================================

An alternative I considered was trying to work with the rownumber() or rank() functions, but I ended up thinking this was a simpler solution for you. it seemed as if you might be doing a cleanup type process and that this process would not be staying around for long.
0
 

Author Comment

by:coperations07
Comment Utility
Right, I try to avoid using cursors unless I can't figure out another way. This is a cleanup type process, but it will be used consistently.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I am working on an alternate solution using the rownumber() function.  It has a small glitch right now. but I hope to overcome it.


I have some test tables i play with. So I cobbled together an update based on disparate columns and it updates fine. Except when i select, it shows me my subset rows, but when I update it updated ALL the rows regardless of my matching criteria.  i know its syntax, but at the moment it escapes me. Taking a short break and maybe it will jump out at me beacuse it LOOKS so dratted simple..  it has to be something I am doing that is exceptionally stupid right now...  

Anyway, here is my sample. Maybe you can see what I am doing wrong in the update.


drop TABLE  #tempcount
go
create TABLE  #tempcount (CustomerNo varchar(20), UpdateCount int)
go

insert into #tempcount (CustomerNo , UpdateCount )
select bk_customer, COUNT(*) from dbo.bookings
group by bk_customer
order by  bk_customer
go
select * from #tempcount
;
with bks_numbered
as
(
Select bk_salesperson as salesperson, bk_customer as customer
 ,row_number() over (partition by bk_salesperson
                         order by bk_salesperson)
                               AS 'Row'                          
FROM   dbo.bookings
)
update dbo.bookings
set bk_customer = CustomerNo
from bks_numbered inner join
#tempcount on customer = CustomerNO
where [Row] <= UpdateCount

;

I don't have your table structures adnsample data. but if you go top to bottom and replace my variables with yours it might make sence to you immediately.  If not, send me your ddl and some sample data and I will rewrite against your testing situation.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
have not been able to get it to work with the update. It consistently updates all the rows in my table when I join to a variable. When I use a fixed value in the same place it works.  (the following worked whiel the variable does not. So I am doing more research to see if updates are not allowed or there is s abug reported against it, or again I just have my syntax off somehow. I had someone else look at it and they sugegsted backing up and calculating the value in a seperate piece of code within a while construct... Backto cursor...


Anyway...   the following does work with a hardcoded value..... :-(

drop TABLE  #tempcount
go
create TABLE  #tempcount (CustomerNo varchar(20), UpdateCount int)
go

insert into #tempcount (CustomerNo , UpdateCount )
select bk_customer, COUNT(*) - 100 from dbo.bookings
group by bk_customer
order by  bk_customer
go
select * from #tempcount
;
with bks_numbered
as
(
Select bk_customer as customer
 ,row_number() over (partition by  bk_customer
                         order by  bk_customer)
                               AS 'MyRow'                          
FROM   dbo.bookings
)
--update dbo.bookings
--set bk_customer = 'C1010444'
select *
from bks_numbered inner join
#tempcount on customer = CustomerNO
where [MyRow] < 125 -- UpdateCount
and customer = CustomerNO

;
0
 

Author Comment

by:coperations07
Comment Utility
thanks for all the help! yea it seems like most things I do end up having some pesky little problem that trips me up. sorry man, I'm pretty new to sql server...I'm not sure what a ddl is.
T-issues.csv
0
 

Author Comment

by:coperations07
Comment Utility
sorry for the double post. the attachment description box got a little quirky on me. The attachments are data from the two tables I'm trying to update.
T-order.csv
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
Please post create statements for tables...

(DDL is old school for data definition language...  And it does not matter how long you have done anything.  Something is alwasy waiting around the corner to trip you up!  At 56 now, my therory remains that if you are not makinig mistakes, you aren't doing anything...

I also posted the problem I ran into under a seperate thread....
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
ralmada posted the answer to my problem. I needed to alias the tables and more importantly (I think) place the qualification on the join criteria !!  here is the revised example for anyone following this issue here...


drop TABLE  #tempcount
go
create TABLE  #tempcount (CustomerNo varchar(20), UpdateCount int)
go

insert into #tempcount (CustomerNo , UpdateCount )
select bk_customer, COUNT(*) from dbo.bookings
group by bk_customer
order by  bk_customer
go
select * from #tempcount
;

;with bks_numbered
as
(
Select bk_customer as customer
 ,row_number() over (partition by  bk_customer
                         order by  bk_customer)
                               AS 'MyRow'                          
FROM   dbo.bookings
)
update a
set a.customer = 'C1010333'
from bks_numbered a inner join
#tempcount b on a.customer = b.CustomerNO and a.[MyRow] < b.UpdateCount
0
 

Author Comment

by:coperations07
Comment Utility
0
 

Author Comment

by:coperations07
Comment Utility
Here's script for the tables. Is this what you need?
t-issues-script.sql
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
Thats what I wanted.  Thanks...
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
See if this works for you:


drop TABLE  #tempcount
go
create TABLE  #tempcount (agin varchar(15), UpdateCount int)
go

insert into #tempcount (agin , UpdateCount )
select a.agin, COUNT(*) from dbo.T_issues a
inner join dbo.T_order b on a.agin = b.agin
where a.mrg_nbr = b.mrg_nbr
--and b.mrg_nbr = '1925225'
and b.scanned = 'N'
and (a.stat_cd = 'V')
group by a.agin
order by  a.agin
go


select * from #tempcount
;

;with agin_numbered
as
(
Select agin , stat_cd
 ,row_number() over (partition by  agin
                         order by  agin)
                               AS 'MyRow'                          
FROM    t_issues  
)
update a
set a.stat_cd = 'X'
from  agin_numbered a inner join
#tempcount b on a.agin = b.agin and a.[MyRow] <= b.UpdateCount
0
 

Author Comment

by:coperations07
Comment Utility
Well I've been tweaking it alot and I'm still stuck on the part below. I tried several different things, but it always counts every instance of 'V' for the agin in the t_issues table regardless of how many scanned = 'N's there are in the t_order table. (I'm using t_order_cmpl table for testing)

      select a.agin, COUNT(1) from dbo.T_order_cmpl a
      left join dbo.T_issues b on a.agin = b.agin
      where a.mrg_nbr = b.mrg_nbr
      and a.mrg_nbr = '009446348'
      and a.scanned = 'N'
      and (b.stat_cd = 'V')
      group by a.agin, a.id
      order by  a.agin
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I went back to the test data you gave me.


Update [Test].[dbo].[T_order]
set scanned = 'Y'
go

Update [Test].[dbo].[T_order]
set scanned = 'N' where agin in (4583705 )
go

Update [Test].[dbo].[T_order]
set scanned = 'N'
go



The output of the query was

agin      (No column name)
4583705      5

agin      (No column name)
4583705      5


 These are all for the same manager  9441734 and was using the logic

drop TABLE  #tempcount
go
create TABLE  #tempcount (agin varchar(15), UpdateCount int)
go

insert into #tempcount (agin , UpdateCount )
select a.agin, COUNT(*) from dbo.T_issues a
inner join dbo.T_order b on a.agin = b.agin
where a.mrg_nbr = b.mrg_nbr
--and b.mrg_nbr = '1925225'
and b.scanned = 'N'
and (a.stat_cd = 'V')
group by a.agin
order by  a.agin
go


select * from #tempcount
;

;with agin_numbered
as
(
Select agin , stat_cd
 ,row_number() over (partition by  agin
                         order by  agin)
                               AS 'MyRow'                          
FROM    t_issues  
)
update a
set a.stat_cd = 'X'
from  agin_numbered a inner join
#tempcount b on a.agin = b.agin and a.[MyRow] <= b.UpdateCount

So it updated the 5 issues found for this agin....


Somewhere I must be misunderstanding you intentions.  Would you mind explaining in business terms what is being accomplished?  


The following query returns a count of 1 in the same situation.  as it basicaly says that for any case where you have an issue, go look for unscanned box records and count the records with matching order numbers and tell me how many you find. In this case there are multiple issues for this amnage, but only 1 order row.  So do you want the number of order rows or the number of issue rows?  That is why I think we have a communication disconnect.  Actually, it seems to be that they should be tracking and matching on box number.  they are going to be in aworld of hurt if they do not get that resolved early on :-) IMHO


      select a.agin, COUNT(*) from dbo.T_order a
      where exists (select * from  dbo.T_issues b  
      where a.mrg_nbr = b.mrg_nbr and a.agin = b.agin
      and a.mrg_nbr = '9441734'
      and a.scanned = 'N'
      and (b.stat_cd = 'V'))
      group by a.agin
0
 

Author Comment

by:coperations07
Comment Utility
Sure.  This is for a quality control app.  The order is being filled on a sorting machine. There can be several cartons per order filled on the machine, but only one at a time.  QC wants to check these cartons as they come off the machine.  However, the data only gets populated in the database when the packages drop in the carton,.

So if they are checking one carton before the entire order is complete and one agin(package) is in the carton, but should be in the next carton then that agin is flagged as an 'over' meaning there's more than required for the order. So when it's scanned, an over record is written to the t_issues table.

When the next carton is checked they scan the barcode and the data for that carton is brought in to the db(t_order). Since that agin(from before) was already scanned and called an over it will not be scanned again, so the 'scanned' flag will stay at 'N' in t_order instead of changing to 'Y'.
0
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
Comment Utility
And you are trying to automatically clear normal cases like this  by assuming that if you can match an issue to a single item in a box set to an unscanned status that it must be one of these cases and you want to ....

Set the box scanned flag to a 'y' AND the issue to used (i.e. consumed), hence the...
(just a helpful hint:  you will save youself a ton of hurt if you set standard aliases for your tables and use them consistently for instand recognition.    for me i - issues o = orders.. )

     i.stat_cd = 'X'
     o.scanned = 'Y', o.in_seq = 'N'

I think the last bit of code I sent you does what you want.  test it out and tell me if you agree.  Here it is in full with the exists logic so you do not get permutations in your counts (nut only the exact number non-scanned boxes scanned as the exact count)

drop TABLE  #tempcount
go
create TABLE  #tempcount (agin varchar(15), UpdateCount int)
go

insert into #tempcount (agin , UpdateCount )
      select a.agin, COUNT(*) from dbo.T_order a
      where exists (select * from  dbo.T_issues b  
      where a.mrg_nbr = b.mrg_nbr and a.agin = b.agin
      and a.scanned = 'N'
      and (b.stat_cd = 'V'))
      group by a.agin

go


select * from #tempcount
;

;with agin_numbered
as
(
Select agin , stat_cd
 ,row_number() over (partition by  agin
                         order by  agin)
                               AS 'MyRow'                          
FROM    t_issues  
)
update a
set a.stat_cd = 'X'
from  agin_numbered a inner join
#tempcount b on a.agin = b.agin and a.[MyRow] <= b.UpdateCount


======================================================
If this works, you can apply the same logic against the orders table using the same temporary table.

What I am really thinking you want, though, is the minimum of the bidirectional match.  Because the UpdateCount needs to be applied in pairs. if you do not ahve a pair. It must be a different problem and needs to be reviewed manually?

So using the exists types logic obtain the reverse count and set the UpdateCount to the lessor of the two for the updates of both the issues and orders tables.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I would choose to do that by adding a second tempcount table to contain the alternate counts.  Then joining that to the first table and selecting the min via a case statement.

It could probably be done in a single more complex statement. But I find over time that it is easier to maintain code that is broken down more procedurally.  So unless I see performance issues, I try to seperate my control logic from the execution....
0
 

Author Comment

by:coperations07
Comment Utility
ok, I think this is going to work. I'm just trying to massage it a little bit still. The exists part is only count the matching number of instances like I was going for.

Thanks,
Dave
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I should have noticed the permuatated join much earlier.  I was so taken with feeding it into the row_number logic that I ignored checking the basics first ...  ( I am so sorry I ran you around so much ! If you rate me low on this one, I will certainly understand!)
0
 

Author Comment

by:coperations07
Comment Utility
sorry man, I'm not rating anyone low that puts this much effort into helping me out!  

I still don't quite have it yet. Some of this syntax is new to me. Can you give me a quick breakdown of what this part does?:

;with agin_numbered
as
(
Select agin , stat_cd
 ,row_number() over (partition by  agin
                         order by  agin)
                               AS 'MyRow'                          
FROM    t_issues  
)
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
It creates an immediately usable result set named 'agin_numbered'

Within the result set, the rows have been grouped (partitioned) by again and numbered sequentially and the sequential numbering saved as a variable (MyRow; but you could name it whatever you want)with the data (I selected agin and stat_cd but other variables could also have been included)


The row_number() over Partition tells it to start numbering from one for each thing you partition on.  In this sample issues within agins.... (Issue #1 for this agin, issue #2 for this agin, ...)

This result set is passed to the update and somehow the genius of the people at microsoft allow you to update as long as you specify the update in the syntax shown and it relates the update back to the base table the result set was developed on.  But allows you to also reference the row_numbering that You added along the way.
0
 

Author Closing Comment

by:coperations07
Comment Utility
Thanks for the help. Looks like this is going to get it done.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now