Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

thepastor (Programmer) (OP)
4 Jan 12 11:25
I need to update code1 and code2 in table2 from table1.

Code1 and Code2 of table2 must receive the values in table one according to the counts field for example -- The first record of table1 tells me that the first 5 records of table2 that have a matchfld of "fname1" - update code1 with "ABCD" and code2 with "1", Then the next record in table1 tells me the next 3 records of table2 (not coded with a matchfld of "fname1") I need to update code1 with "ABCD" and code2 with "2" and so on.

TABLE2 will be in order by matchfld. TABLE1 will be in order by matchfld and code2.

This is a small sample as the actual data in table 2 may contain
hundreds of thousands of records. Please help!

TABLE1

counts   code1   code2   matchfld
5        ABCD    1       fname1
3        ABCD    2       fname1
7        EFGH    1       fname2
2        EFGH    3       fname2


TABLE2

matchfld     code1     code2     product
fname1                             doda
fname1                             doda
fname1                             doda
fname1                             doda
fname1                             doda
fname1                             doda
fname1                             doda
fname1                             doda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname2                             deda
fname3                             ohno
fname3                             ohno
fname3                             ohno
fname3                             ohno
fname3                             ohno
fname3                             ohno
 
gmmastros (Programmer)
4 Jan 12 11:58
Is this a "once and done" thing, or do you need to be able to run this update on a regular basis?

For example, you could run the following query, which will create a set of other queries that you can run.

CODE

Select 'Update Top (' + Convert(VarChar(10), Counts) + ') Table2
        Set    Table2.Code1 = ''' + Table1.Code1 + ''',
               Table2.Code2 = ''' + Table1.Code2 + '''
        From   Table1
               Inner Join Table2
                  On Table1.matchfld = Table2.matchfld
        Where  Table2.Code1 Is NULL
               And Table2.Code2 Is NULL'
From    Table1

When you run the query above, you should see a set of results where each result is a query you can run in a query window.  Just copy paste the results of the query above in to a new query window and run it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

thepastor (Programmer) (OP)
4 Jan 12 12:10
Wow, this is a great start. And I sure do thank you.

This is a process I run on different tables 9 or 10 times a day every day. It's always the same so the only thing that ever changes is the table names.

I'd love to be able to pass the name for table1 and table2 as parameters and never change the code. That would be ideal.

I really appreciate your fast response.
gmmastros (Programmer)
4 Jan 12 13:12
This is super ugly because we effectively have a query that build other queries but is run dynamically.

Notice in the code below the declaration of @Table1 and @Table2.  To run this on any other set of tables, all you need to do is change the 2 lines where the variables are getting assigned to a value.

CODE

Declare @Table1 VarChar(100),
        @Table2 VarChar(100),
        @SQL VarChar(8000),
        @i Int,
        @Max Int
        
Declare @Queries Table(RowId Int Identity(1,1), SQL VarChar(8000))

Set @Table1 = 'Table1'
Set @Table2 = 'Table2'


Select  @SQL = '
Select ''Update Top ('' + Convert(VarChar(10), Counts) + '') ' + @Table2 + '
        Set    ' + @Table2 + '.Code1 = '''''' + ' + @Table1 + '.Code1 + '''''',
               ' + @Table2 + '.Code2 = '''''' + ' + @Table1 + '.Code2 + ''''''
        Where  ' + @Table2 + '.matchfld = '''''' + ' + @Table1 + '.matchfld + ''''''
               And ' + @Table2 + '.Code1 Is NULL
               And ' + @Table2 + '.Code2 Is NULL''
        From   ' + @Table1

Insert Into @Queries(SQL) Exec (@SQL)

Select    @i = 1, @Max = Max(RowId)
From    @Queries

While @i <= @Max
    Begin
        Select @SQL = SQL
        From   @Queries
        Where  RowId = @i
        
        Exec (@SQL)
        
        Set @i = @i + 1
    End

 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

thepastor (Programmer) (OP)
4 Jan 12 14:57
Awesome, Thanks so much George. This has been a great help. And a huge time saver.

Not ugly to me, you should see some of the garbage I write. I'm a foxpro prorammer being forced into T-SQL so I need all the help I can get.

Thanks again.
 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close