Solved

sql update X number of records

Posted on 2012-04-12
29
329 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
ID: 37839061
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
ID: 37839580
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
ID: 37839881
And there is no relationship between that second key at all and the oposite table?
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37840029
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
ID: 37840041
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
ID: 37842313
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
ID: 37853205
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
ID: 37855562
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
ID: 37855903
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
ID: 37856059
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
ID: 37856303
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
ID: 37856446
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
ID: 37856476
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
ID: 37856513
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
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37856686
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
ID: 37857411
0
 

Author Comment

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

Expert Comment

by:SJCFL-Admin
ID: 37857443
Thats what I wanted.  Thanks...
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37857629
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
ID: 37863325
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
ID: 37865682
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
ID: 37865835
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
ID: 37866026
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
ID: 37866063
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
ID: 37873081
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
ID: 37873138
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
ID: 37873728
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
ID: 37873819
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
ID: 37910906
Thanks for the help. Looks like this is going to get it done.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 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