Update column with itself plus value of column in another table -- accumulating?

  • This was a 20-second operation in Access, but in SQL it's like pulling Leprechaun teeth!

    I've got two tables -

    one with distinct addresses, a count of the number of (table2) records with that address and an empty column for political parties. Another table -- one record for each voter with their address (formatted to join to other table), and their party.

    The aim is to accumulate a distinct list of parties represented by a single address in table "1" by walking an INNER JOIN, updating the parties column where the value of table2's "party" is not found in table1's "parties" column. I've spent ten hours on this already, trying to do everything in SQL now, and what I've concluded is that I cannot write an Update query where a column can be assigned it's existing value PLUS something else. I cannot get at the existing value for some reason. I also cannot assign that value to a variable and use THAT, so a query is apparently not really a loop so much as a closed, encapsulated instance?

    I don't even think CHARINDEX is evaluating the contents of the target column! I'm just getting the FALSE response in the CASE WHEN statement and getting the first value, but again, no future concatenation when other table2 records are encountered that should evaluate TRUE, and add their values.

    I've searched the forums and I found this one:

    declare @lsOrderID varchar(8000)

    update

    tblCustomerOrders

    set

    @lsOrderID = IsNull(@lsOrderID,'') + a.OrderID + ','

    from

    tblCustomerOrders a

    where

    a.CustID = '1001'

    go

    BUT this doesn't get the value of @IsOrderID into a table column! (it also is a mystery to me how this works without a JOIN!)

    Any help would be appreciated! And if I have to go FETCH/CURSOR, does anyone know of a tutorial that doesn't start at mile-marker 10,000?? I've found numerous examples but they're all incredibly complicated and difficult to dissect. I suspect the @ and @@ are about passing by reference, but I haven't found any good docs on it. I have several books on SQL, and they all suck.

    Thanks!

  • Hi Hugh

    you may find that if you follow the advice in the link below that you get faster amd more detailed response...paritcularly if you provide example of what you expect the results to be.....just my two'penneth

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I think I know what you are asking but it would help if you would provide the DDL for your tables, sample data for the tables, and expected results when all is said and done.

    Please read the first article i have referenced in my signature block regarding asking for help. Following the instructions for posting the requested information and I'm sure several people will jump in and assist, and provide you with tested code in return.

  • Thanks for the quick response! I'll read and digest the link you posted, but also, here is a sample of what I've got, and what I'm trying to get:

    in table "1",

    addrmembersparties

    320|W|Valencia Mesa|Dr||Apt|7|928352 ' (a big blank field, NOT NULL!, '') '

    In that row, I've got a concatenated address string, to isolate multiple instances at the same address. A previous COUNT query produce the distinct row, and the count of "members", and there is an empty column for the accumulated "parties"

    In table "2", I have the individual records

    voter_idAddrparty

    3071058 320|W|Valencia Mesa|Dr||Apt|7|92835REP

    982764 320|W|Valencia Mesa|Dr||Apt|7|92835DEM

    What I'm trying to do is INNER JOIN tables 1 & 2 and "assemble" column "parties" by conditionally ADDING the value of "party" (table 2) TO IT where it isn't already there.

    What I want "parties" to wind up with is:

    |REP|DEM

    Because party abbreviations are not all 3 chars, I use the "bar" character to separate them. Counting the bars, reveals the "distinct" parties at that address! This is essential for building stats like "households" (vs. single voters) and "pure party" households.

    What I've tried is:

    Update oc_daddr

    SET oc_daddr.parties = Rtrim(oc_daddr.parties) + '|' + Rtrim(oc_addr.party)

    FROM oc_daddr INNER JOIN oc_addr

    ON oc_daddr.addr = oc_addr.addr

    where daddr is the distinct table with empty "parties" and addr is the individual voter records.

    That was just to see if I could get the concatenation to work at all! It isn't even the "DISTINCT" one (that's here):

    Update oc_daddr

    SET oc_daddr.parties =

    CASE WHEN (CharIndex(Ltrim(Rtrim(oc_addr.party)),oc_daddr.parties,1)>0)

    THEN oc_daddr.parties

    ELSE oc_daddr.parties + '|' + Rtrim(oc_addr.party)

    END

    FROM oc_daddr INNER JOIN oc_addr

    ON oc_daddr.addr = oc_addr.addr

    (and every possible permutation of variable use and column use!) I tried declaring a variable and assigning it the value of oc_daddr.parties, before starting to assign value to THAT, but the variable either didn't get the value, or wasn't accessible inside the CASE WHEN statement.

    Obviously everything I've tried so far has bombed miserably.

    The best I've gotten so far is the first party instance (of 'x' rows that matched address), but CHARINDEX never produces a positive result after that, causing the new value to be appended!

    (as far as I know)

  • Hey, know what I did there? I posted bad data BEFORE reading your post! 🙂

    Here's table "1" -- the accumulator

    USE [CPI]

    GO

    /****** Object: Table [dbo].[oc_daddr] Script Date: 08/05/2009 12:51:50 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[oc_daddr](

    [addr] [nvarchar](81) NULL,

    [members] [int] NULL,

    [parties] [varchar](50) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    Here's table "2" -- the "detail"

    USE [CPI]

    GO

    /****** Object: Table [dbo].[oc_addr] Script Date: 08/05/2009 12:50:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[oc_addr](

    [voter_id] [nchar](8) NULL,

    [Addr] [nvarchar](81) NULL,

    [party] [varchar](5) NOT NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    I'm using a concatenated address instead of "UniqueID" (in SQL 2005) because a CLSID is actually LONGER than an address!

    The "Create Some Data" part of the "tutorial" is complete gibberish since it avoids showing how to assign static values to ANYTHING! (Kee-rist, who comes up with this stuff!?)

    I see it uses system "constants" -- do I put my field names there, or the values in single quotes?

    What do the four single quotes do? Do I need them?

    The "example" is shown to produce 8 rows of data? How did that happen? Why didn't it produce 1, or 9?

  • The "Create Some Data" part of the "tutorial" is complete gibberish since it avoids showing how to assign static values to ANYTHING! (Kee-rist, who comes up with this stuff!?)

    I see it uses system "constants" -- do I put my field names there, or the values in single quotes?

    What do the four single quotes do? Do I need them?

    The "example" is shown to produce 8 rows of data? How did that happen? Why didn't it produce 1, or 9?

    Hi Hugh

    "Create some data" requires that you have already got some sample data in "yourtable"...you then need to amend the code to reflect the column names in "yourtable"...eg alter QUOTENAME(DateValue,'''') to QUOTENAME(yourtable_columnname,'''')

    Yes you do need the quotes.

    Create some sample tables, populate with enough sample data that will detail your issue, follow the instructions in the link provided....then please post back.

    As for gibberish :blush:...well all I will say is that type of comment isnt particularly polite especially when you are asking people to help

    I think that if you read some of the other posts on this site that you will find many examples of how other people have managed to follow the instructions and have received fast and detailed answers

    Kind regards Graham

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • hugh.hemington (8/5/2009)


    The "Create Some Data" part of the "tutorial" is complete gibberish since it avoids showing how to assign static values to ANYTHING! (Kee-rist, who comes up with this stuff!?)

    I see it uses system "constants" -- do I put my field names there, or the values in single quotes?

    What do the four single quotes do? Do I need them?

    The "example" is shown to produce 8 rows of data? How did that happen? Why didn't it produce 1, or 9?

    You are the first person to complain about Jeff Moden's article. It has helped many others with their questions/problems because it has shown them how to post the DDL for the tables, create sample data for those tables in format that is readily consummable by those who are willing to help.

    If you want tested code that provides the answers you are expecting, then that is the least you can do for us. We are not paid to help, we do so because others have taken the time to help us when we needed the help. It is part of giving back to our community, and SSC is a community.

  • hugh.hemington (8/5/2009)


    The "Create Some Data" part of the "tutorial" is complete gibberish since it avoids showing how to assign static values to ANYTHING! (Kee-rist, who comes up with this stuff!?)

    I see it uses system "constants" -- do I put my field names there, or the values in single quotes?

    What do the four single quotes do? Do I need them?

    The "example" is shown to produce 8 rows of data? How did that happen? Why didn't it produce 1, or 9?

    Heh ... perhaps a primer on T-SQL would be more appropriate for you. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • My apologies. The nugget I was missing was that the object in the QUOTENAME( was a column name and not a variable or literal.

    This data sample loads to oc_daddr -- the distinct address table, where I'm trying to accumulate a distinct list of parties

    SELECT '0||Cliff Dr & Jasmine|St||||92651','1','' UNION ALL

    SELECT '0||Dana Pt|Hbr||||92629','2','' UNION ALL

    SELECT '1||Abbey|Ln||||92656','2','' UNION ALL

    SELECT '1||Acacia Tree|Ln||||92612','7','' UNION ALL

    And this goes in oc_addr -- with one row per voter

    SELECT '1671384 ','0||Cliff Dr & Jasmine|St||||92651','DS ' UNION ALL

    SELECT '2321494 ','0||Dana Pt|Hbr||||92629','DEM ' UNION ALL

    SELECT '2321495 ','0||Dana Pt|Hbr||||92629','DEM ' UNION ALL

    SELECT '2129246 ','1||Abbey|Ln||||92656','AI ' UNION ALL

    SELECT '2879865 ','1||Abbey|Ln||||92656','REP ' UNION ALL

    SELECT '3239964 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    SELECT '469098 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    SELECT '1886308 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    SELECT '3135629 ','1||Acacia Tree|Ln||||92612','DS ' UNION ALL

    SELECT '3084979 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    SELECT '2632629 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    SELECT '2778756 ','1||Acacia Tree|Ln||||92612','DEM ' UNION ALL

    The oc_daddr.parties for the Acacia Tree Ln address should wind up with a value of '|DEM|DS'

    with the repeated 'DEM' values distinct, and those trailing 'DS' not added because that value is already there.

    and Abbey Ln should be '|AI|REP'

    Cliff Dr, and Dana Point should have single parties in 'parties'.

    That's the target, and what I did previously in Access (and I'd prefer NOT to do in Access anymore)

    I do appreciate the help and I'm sorry the frustration of many hours of fruitless attempts impacted my previous post.

  • Could you recommend a good T-SQL primer?

    I have "Wrox Press Beginning T-SQL with Microsoft SQL Server 2005 and 2008"

    I looked up "QUOTENAME" in that book, but when the brief description didn't look anything like the way it was used in your primer, it threw me off.

    Is there a (much) better primer on T-SQL?

    I started working with databases when dBASE II came out, went on to 'x-base', FoxBase and Access, so my entire history is apparently at a 90-degree tilt to the way SQL works. I got good at writing "SQL" in Access, but I now realize that was a fairly perverted 'slang' of actual SQL. So I've got some learning to do. Any sources you could suggest would be appreciated.

  • To begin with, you've found out that this doesn't work.

    UPDATE d SET parties = parties

    + CASE WHEN CHARINDEX(RTRIM(a.party), d.parties) = 0

    THEN '' ELSE '|' + RTRIM(party) END

    FROM dbo.oc_daddr d

    INNER JOIN dbo.oc_addr a ON a.Addr = d.Addr

    If you highlight this code and get the execution plan (button on toolbar, Query menu, or press Ctrl-L) you'll see that all rows are joined but it uses a Stream Aggregate operation to select only one update for each row. This is the way it is supposed to work, in spite of the way Access or dBase are implemented. If you have specified multiple updates for a row in the same UPDATE statement, the last one supercedes (not adds to) all the others.

    Here are some methods that work. Hopefully you will learn something by comparing them. Remember to set all the parties fields to '' between methods.

    You can use a cursor to update one row at a time, using the multiple-update-of-a-variable technique. Variables can be updated multiple times in one UPDATE statement because they don't have to follow the same ANSI rules for SQL behavior.

    DECLARE @addr NVARCHAR(81)

    DECLARE @parties VARCHAR(50)

    DECLARE p CURSOR FAST_FORWARD READ_ONLY FOR SELECT Addr FROM dbo.oc_daddr

    OPEN p

    WHILE 1=1 BEGIN

    FETCH NEXT FROM p INTO @addr

    SET @parties = ''

    SELECT @parties = @parties + '|' + party

    FROM (SELECT DISTINCT RTRIM(party) AS party

    FROM dbo.oc_addr

    WHERE Addr = @addr) a

    IF @@FETCH_STATUS 0

    BREAK

    UPDATE dbo.oc_daddr SET parties = @parties WHERE Addr = @addr

    END

    CLOSE p

    DEALLOCATE p

    You can update more than one row at a time by updating every row where a party is missing, and repeating until no more updates are made.

    WHILE 1=1 BEGIN

    UPDATE d SET parties = d.parties + '|' + a.party

    FROM oc_daddr d

    INNER JOIN (

    SELECT Addr, RTRIM(LTRIM(party)) AS party

    FROM oc_addr) a ON d.Addr = a.Addr

    WHERE CHARINDEX(a.party, d.parties) = 0

    IF @@ROWCOUNT = 0

    BREAK

    END

    There is a more advanced way to do concatenation by using XML subqueries. I'm not going to explain how this works, but this query can produce the entire oc_daddr table directly from oc_addr.

    SELECT * FROM (

    SELECT Addr, COUNT(*) AS members

    FROM oc_addr

    GROUP BY Addr

    ) a

    CROSS APPLY (

    SELECT '|' + party

    FROM (

    SELECT DISTINCT LTRIM(RTRIM(party)) AS party

    FROM dbo.oc_addr

    WHERE Addr = a.Addr

    ) x

    FOR XML PATH('')

    ) p (parties)

    You can also use an XML subquery in the UPDATE statement you were attempting to write in the first place.

    UPDATE oc_daddr SET parties = (

    SELECT DISTINCT '|' + RTRIM(party)

    FROM oc_addr

    WHERE oc_addr.Addr = oc_daddr.Addr

    FOR XML PATH(''))

  • So with four or more ways to do it, I found..... none of them! 🙂

    Yeah, my head is still in row-wise manipulation but I'm trying. I've found several CURSOR/FETCH examples and I know it's a powerful construct. I've been looking forward to learning it, but until now, the examples I've had were 60+ lines long and pretty hard to dissect. Yours seems more accessible, and since it applies to what I'm trying to do, it should provide an answer, and a good learning platform.

    Thanks for your help!

  • hugh.hemington (8/6/2009)


    So with four or more ways to do it, I found..... none of them! 🙂

    Yeah, my head is still in row-wise manipulation but I'm trying. I've found several CURSOR/FETCH examples and I know it's a powerful construct. I've been looking forward to learning it, but until now, the examples I've had were 60+ lines long and pretty hard to dissect. Yours seems more accessible, and since it applies to what I'm trying to do, it should provide an answer, and a good learning platform.

    Thanks for your help!

    Cursors may be powerful, but they aren't scaleable and should be avoided whenever possible.

  • Don't use the While Loop examples, either. They're nothing but cursors without the word "cursor" in them. Cursors and While Loops are very bad ways to go compared with what are known as "set based" methods.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hugh.hemington (8/6/2009)


    Could you recommend a good T-SQL primer?

    I have "Wrox Press Beginning T-SQL with Microsoft SQL Server 2005 and 2008"

    I looked up "QUOTENAME" in that book, but when the brief description didn't look anything like the way it was used in your primer, it threw me off.

    Is there a (much) better primer on T-SQL?

    I started working with databases when dBASE II came out, went on to 'x-base', FoxBase and Access, so my entire history is apparently at a 90-degree tilt to the way SQL works. I got good at writing "SQL" in Access, but I now realize that was a fairly perverted 'slang' of actual SQL. So I've got some learning to do. Any sources you could suggest would be appreciated.

    Heh... I understand where you're coming from. SQL is not "SQL" and T-SQL (the name of the flavor of SQL for SQL Server) is quite different. Once you get used to it's paradigms, it's actually a lot easier than many other types of SQL. Like those other versions (including Oracle), set based code will normally beat cursors and while loops even though the perception is that some of them are optimized for cursors and while loops. SQL Server will let you make such constructs, but the basis of the underlying engine is set based code.

    Has anyone introduced you to "Books Online" yet? It's the help system for SQL Server and, although it's not much of a tutorial, it's a great reference and does have some fairly decent examples. Of course, like any book, it does have it's short comings. For example, it tell's you that a single quote may be used as an operand but it doesn't tell you that single quotes are like double quotes in a lot of other languages in that if you want to use one as a literal, you have to double it up. Since single quotes also define a string literal, you need four quotes to make one appear.

    The book you have is probably as good as any although I haven't read it. They all lack something. None of them start out with how SQL Server uses "pseudo cursors" behind the scenes nor how you can use them to your advantage.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply