combine update statements....help...

Hi guys! Is there a way to combine these update statements?

Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @.syain_No", cnn)

Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @.syain_No", cnn)

The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...

Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)

Please help me. Thanks.

Audrey

You can do it in one statement. Understand the consequences first. Lets say you already have some records (say 5) in table TE_shounin_zangyou, your first INSERT will add some more rows to it. Your second UPDATE will update the rows from the insert as well as the existing rows. However, if you combine both the INSERT and the UPDATE into one statement you will only modofy the rows being INSERTED with the SELECT statement. Any pre-existing rows will not be affected. If, in your case, there would be NO pre-existing rows with the condition [syain_No] = @.syain_No, then you can do it all in one statement as follows:

Try this:

INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou,phase,phase_states2,syounin2_sysd)

SELECT syain_No,date_kyou,time_kyou,2,'06',CONVERT(VARCHAR(10),GETDATE(),101) FROM TE_zangyou WHERE [syain_No] = @.syain_No