Update Column based on another Table 1

[orreymalcolm]

Hey guys,

I have a table (AMAC) that I wish to update based on another table with similar columns (OldAMAC). The fields they have in common are the Agreement ID, Member ID and the Committed field.

The reason I'm doing this is because one of the tables (the one I wish to reference to when looking for the value) is old and outdated and missing many records, so I created a new table and got a cartesian product (which I needed).

For the Committed field, the old table used A and B to determine whether the member was commited to a contract or not (A is yes, and B is No), but my new table, i'm using bit (1's and 0's). As of right now, I set every single one to 0 for now (meaning non-committed) and now I wish to update the ones they are committed to.

What I want to do, is write a query to go through the old list and determine which records they are committed to (values of A) and then update the corressponding record in my new table with a 1.

My code so far is as follows (So far it just lists the agreements the member is committed to)

DECLARE @MemberNum int
	SET @MemberNum = 0
	WHILE (@MemberNum < 10000) --Set to one above highest Member Number
		BEGIN
			IF EXISTS (SELECT MemberNum FROM AMAC WHERE cast(MemberNum as int) = @MemberNum)
				BEGIN
					SELECT OT.fm_agre_no, OT.fm_option FROM OldAMAC OT
					WHERE (cast(OT.MemNum as int) = @MemberNum) AND OT.Committed = 'A'
				END
			SET @MemberNum = @MemberNum + 1
		END

Any help is appreciated. Thanks!

 

[gmmastros]

This may not be perfect, but take a look at how this query is put together to get an idea of how this should work.

[COLOR=blue]Update[/color] NewTable
[COLOR=blue]Set[/color]    [COLOR=#FF00FF]Committed[/color] = 1
[COLOR=blue]From[/color]   NewTable
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] OldTable
         [COLOR=blue]On[/color] NewTable.AgreementId = OldTable.AgreementId
         And NewTable.MemberId = OldTable.MemberId
[COLOR=blue]Where[/color]  OldTable.Committed = [COLOR=red]'A'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[orreymalcolm]

PERFECT, exactly what I want, thank you SO much gmmastros!

I'm glad this is finally off my chest. KUDOS!