2 Replies - 603 Views - Last Post: 21 November 2011 - 08:06 PM

#1 marvinmartian  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 07-October 09

update 2 tables in a single query issue

Posted 20 November 2011 - 07:31 PM

is it possible to convert this 2 statements into a single query

UPDATE HR_Appointment
SET CurAppt=(case when (CONVERT(VARCHAR(10),Apptdateto,111))<=(CONVERT(VARCHAR(10),getdate(),111) THEN 0 ELSE 1 END)
WHERE CurAppt=1 and (dbo.Fn_GetTenure(StatusIdx)<>'Permanet') and CtrlNo=477



UPDATE HR_PRPROFILE
SET RemarksIdx=2
WHERE CtrlNo=477 


Is This A Good Question/Topic? 0
  • +

Replies To: update 2 tables in a single query issue

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: update 2 tables in a single query issue

Posted 21 November 2011 - 06:37 PM

Yes this is entirely possible using tranactions. Transactions can be used for any type of query in fact, but they are generally most useful when updating and inserting.

The general syntax of trasnactions is:
BEGIN TRANSACTION
-- Put your first query here
-- Put your second query here
-- etc
COMMIT TRANSACTION
GO



If you are using MS SQL, you may need to seperate your queries with semi-colons (not sure on this one).

There is also a command called ROLLBACK, which will allow you to commence a transaction, and if any errors occur, the entire transaction can be rolled back. This means you can prevent data in your database from being corrupted.

It would be very easy to write a long post about error handling in T-SQL, but luck has it someone has done this already on MSDN. Here is the link:
Error Handling in T-SQL: From Casual to Religious

This post has been edited by e_i_pi: 21 November 2011 - 06:37 PM

Was This Post Helpful? 0
  • +
  • -

#3 marvinmartian  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 07-October 09

Re: update 2 tables in a single query issue

Posted 21 November 2011 - 08:06 PM

many many thanks...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1