SQL Update Statement Help

[philwes83170]

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.

 

[SQLSister]

example update code with joins

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

 

[wannalrn]

you can try this,

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

hope this helps you

 

[alexjones]

Try something like:

UPDATE AccountExtra
  SET Field1 = 'X'
  FROM Account 
  JOIN AccountExtra ON AccountExtra.AccountID = Account.AccountID
 WHERE Account.Field5 = 'X'

 

[philwes83170]

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...

 

[SQLSister]

In that case you need to do an insert statement instead of an update

Questions about posting. See faq183-874