Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Posts
    11

    Unanswered: updating similiar rows in same table

    given a table, x, I would like to update rows that have similiar keys.

    key cost
    -------- ----
    a.1.red 5
    a.1.blue 0
    a.2.red 7
    a.2.blue 0
    b.1.red 3
    b.1.blue 0
    b.2.red 6
    b.2.blue 0

    goal: update x such that a.*.blue = a.*.red where values for * match
    desired result
    a.1.blue 5 (from a.1.red)
    a.2.blue 7 (from a.2.red)

    TIA,
    Larry

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you really using a single keyfield that stores character string values like "a.1.red"? If so, you need to redesign your database following the principles of normalization, or you are going to be running into lots of problems like you are describing.

    If, as they should be, the key fields are separate elements of a multi-column key, then you can easily create a query that updates rows based on similarities in the keyfields. I'd recommend implementing it as a trigger.

    If not, then you are going to need to parse the keyfield (concatenated keys are often called "superkeys", but they should be called "suckeykeys") in order to use individual elements as criteria. The parsing logic will depend on the exact values in your keyfield and will have to accomodate all possible values and combinations. Once parsed, the optimizer can't take advantage of any index on the keyfield, so your query will run slowly if you have a large table.

    blindman

  3. #3
    Join Date
    Dec 2002
    Posts
    11
    The key field is indeed made up of several pieces. However, the number of pieces is variable. The key is really an aggregate of values for attributes about a part. I'm trying to allow for assigning related data to "matching" parts without regard to certain attributes (confused yet?). For example, color is immaterial so a.1.red and a.1.blue can both be assigned the same cost for example. However, inventory can not. I can already assign cost to any part; what I'm trying to do is create user-defined rules that "auto assign" these new values to like items.
    I've solved the problem with brute force but thought some creative SQL could handle my goal.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to redesign your table structure before you do a lot more coding. You should have a table that just uses fields "ab.." and "12..." as an index, where you can store your cost. Available colors would be a secondary subtable. In this way, anytime you update a cost for an item it applies to all the colors.
    This is one of the fundamental rules of database normalization. You need to read up on these principles (lots of websites talk about them), because violations of them can result in untold hours of time spent coding around inappropriate database architecture.

    blindman

  5. #5
    Join Date
    Dec 2002
    Posts
    11
    Treat my multi-part key as a SKU. It just so happens I embed special knowledge in building my SKU value. Now if I wanted to price all Coke Diet as Coke Regular * 1.5 and Coke 12packs as Coke 6pack * 1.75, it would be nice to define some SQL to handle such rules. To make matters worse, Sprite may have different rules/values for similair packaging. Whether the key is Coke.diet.6pk, Coke.regular.12pk or 156fg745 should not matter. In fact I think my naming approach makes the problem a LOT easier even if I have to execute the rule in application code.

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Does this help?

    set nocount on
    go
    create table #xx (myKey varchar(20), myValue int)
    go
    insert #xx (myKey , myValue) values ('a.1.red',99)
    insert #xx (myKey , myValue) values ('a.1.blue',7)
    insert #xx (myKey , myValue) values ('a.2.red',88)
    insert #xx (myKey , myValue) values ('a.2.blue',5)
    insert #xx (myKey , myValue) values ('b.1.red',9)
    insert #xx (myKey , myValue) values ('b.1.blue',1)
    insert #xx (myKey , myValue) values ('b.2.red',8)
    insert #xx (myKey , myValue) values ('b.2.blue',6)
    go
    select myKey , myValue from #xx
    go
    update x2
    set myValue = x1.myValue
    from #xx x1, #xx x2
    where x1.myKey like 'a.%.red'
    and x2.myKey like 'a.%.blue'
    and substring(x2.myKey,1,3) = substring(x1.myKey,1,3)
    go
    select myKey , myValue from #xx
    go

    drop table #xx
    go

    Output
    Code:
    myKey                myValue     
    -------------------- ----------- 
    a.1.red              99
    a.1.blue             7
    a.2.red              88
    a.2.blue             5
    b.1.red              9
    b.1.blue             1
    b.2.red              8
    b.2.blue             6
    
    myKey                myValue     
    -------------------- ----------- 
    a.1.red              99
    a.1.blue             99
    a.2.red              88
    a.2.blue             88
    b.1.red              9
    b.1.blue             1
    b.2.red              8
    b.2.blue             6
    MCDBA

  7. #7
    Join Date
    Dec 2002
    Posts
    11
    Thanks but in the general case it won't work. The problem is the substring [ I started along this path too but ran into trouble ].

    The subkey may be longer than just 1 letter so a simple, hard-coded width won;t work. Plus the pattern may be at the front, %.1.red vs a.%.red.

    Again the foal is MATCHING.1.blue = ANY.1.red or a.MATCHING.blue = a.ANY.red. The ANY part is easy while the MATCHING is hard (for me at least).

    Finally, the number of subkeys is variable.

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You may need to create a view that splits out the key into pieces, then update data through the view.

    Code:
    set nocount on
    go
    create table myTbl (myKey varchar(20), myValue int)
    go
    insert myTbl (myKey , myValue) values ('a.1.red',99)
    insert myTbl (myKey , myValue) values ('a.1.blue',7)
    insert myTbl (myKey , myValue) values ('a.2.red',88)
    insert myTbl (myKey , myValue) values ('a.2.blue',5)
    insert myTbl (myKey , myValue) values ('b.1.red',9)
    insert myTbl (myKey , myValue) values ('b.1.blue',1)
    insert myTbl (myKey , myValue) values ('b.2.red',8)
    insert myTbl (myKey , myValue) values ('b.2.blue',6)
    go
    create view myVw
    as
    select	myKey, 
    	substring(myKey,1,charindex('.',myKey)-1) as part1,
    	substring(substring(myKey,charindex('.',myKey)+1,99),1,charindex('.',substring(myKey,charindex('.',myKey)+1,99))-1) as part2,
    	REVERSE(substring(REVERSE(myKey),1,charindex('.',REVERSE(myKey))-1)) as part3,
            myValue
    from myTbl
    go
    select myKey , myValue from myTbl
    go
    select * from myVw
    go
    
    update x2
    set myValue = x1.myValue
    from myVw x1, myVw x2
    where x1.Part1 = 'a'
    and   x1.Part3 = 'red'
    and   x2.Part1 = 'a'
    and   x2.Part3 = 'blue'
    and   x1.Part2 = x2.Part2
    go
    
    select myKey , myValue from myVw
    go
    
    drop table myTbl
    drop view myVw
    go
    Output
    Code:
    myKey                myValue     
    -------------------- ----------- 
    a.1.red              99
    a.1.blue             7
    a.2.red              88
    a.2.blue             5
    b.1.red              9
    b.1.blue             1
    b.2.red              8
    b.2.blue             6
    
    myKey                part1                part2                part3                myValue     
    -------------------- -------------------- -------------------- -------------------- ----------- 
    a.1.red              a                    1                    red                  99
    a.1.blue             a                    1                    blue                 7
    a.2.red              a                    2                    red                  88
    a.2.blue             a                    2                    blue                 5
    b.1.red              b                    1                    red                  9
    b.1.blue             b                    1                    blue                 1
    b.2.red              b                    2                    red                  8
    b.2.blue             b                    2                    blue                 6
    
    myKey                myValue     
    -------------------- ----------- 
    a.1.red              99
    a.1.blue             99
    a.2.red              88
    a.2.blue             88
    b.1.red              9
    b.1.blue             1
    b.2.red              8
    b.2.blue             6
    I don't think you'll be able to write one generic WHERE clause for a.%.red and %.1.red and a.2.%.
    MCDBA

  9. #9
    Join Date
    Dec 2002
    Posts
    11
    But with a variable number of subkeys, I guess I would have to create an "upper limit" number of items (part1, ... partn) in the view.

    Thanks for the tips. Definitely something to experiment with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •