Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Update Statement Help

Status
Not open for further replies.
Mar 4, 2003
47
US
I have two tables Account and AccountExtra. Account is the primary table and the key is AccountID. AccountExtra is Left Joined via AccountID, since not every account will have data for the extra fields.

I need to update a field in the AccountExtra table when a value is true in one of the Account table fields. I'm not a very experienced SQL user so I'm having trouble working out the syntax. Help Please...

I need something like this:

Update AccountExtra
Set AccountExtra.Field1 = 'X'
Where Account.Field5 = 'X'

I'm not sure how to work out the joins into the syntax.
 
example update code with joins
Code:
update t1
set field1 = t2.field2
from table1 t1 join table2 t2
on t1.somefield = t2.somefield
where t2.field3 = 'test'

Questions about posting. See faq183-874
 
you can try this,

Set AccountExtra.Field1 = 'X'
Where AccountID = (select AccountID from Table where Account.Field5 = 'X')

hope this helps you
 
Try something like:

Code:
UPDATE AccountExtra
  SET Field1 = 'X'
  FROM Account 
  JOIN AccountExtra ON AccountExtra.AccountID = Account.AccountID
 WHERE Account.Field5 = 'X'
 
Thanks so far for the help but nothing works yet. The problem seems to be that the AccountExtra table may not have an AccountID yet. My Data looks like this:

Account Table
AccountID | Name | Field1
12345 | Test1 | x
12346 | Test2 | z

AccountExtra Table
AccountID | Field2
12345 | x

So when I query them I'll use a left join and get:

AccountID | Name | Field 1 | Field 2
12345 | Test1 | x | x
12345 | Test2 | z | NULL

I need to set Field2 to a value when Field1 is z, but the AccountID does not exist yet in the second table...
 
In that case you need to do an insert statement instead of an update

Questions about posting. See faq183-874
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top