can I do a combined update?

[karen4201]

I'm not sure what it would be called, but could this be done?

Just as how I could do an INSERT by using a SELECT statement, such as:
insert into tableA(the_id, the_date)
select tableB.the_id as [the_id], '2006-01-01' as [the_date]
from tableB left outer join tableB_other
on tableB.the_id = tableB_other.the_id
where tableB_other.the_id is null


I'd like to do an UPDATE by using a SELECT statement. So maybe something like this

update tableA set the_date=B where the_id=C
select the_id as [C] from tableA where the_date is null

Could this be done? How would this be done? What would the syntax be?

Thanks in advance for any suggestions.

 

[gmmastros]

Generically....

Update Table1
Set    Table1.Field = Table2.Field
From   Table1
       Inner Join Table2 On Table1.SomeField = Table2.SomeField
Where  Table1.OtherField = 'Something'
       And Table2.AnotherField = 1

I used generic code because it's not clear to me what you are trying to accomplish with your update statement.

-George

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

 

[karen4201]

can the update be performed by only doing a set number of items, such as when you do a "select top 20 * from..." ?

 

[SQLSister]

Yes but then you do this with a derived table in the join

Update table1
set field1 = a.field1
from table1 t join
(select top 20 field1, field2 from table2 where date>'01/01/2006') a on t.field2 = a.field2

Questions about posting. See faq183-874
Click here to help with Hurricane Relief