TSQL Help Please On Update of Tables

[thepastor]

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]

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.

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]

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]

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.

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]

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.