How to update multiple tables in one statement ?

Question

How to update multiple tables in one statement ?

SQL Kidu

Ten Centuries

Points: 1296
More actions
September 20, 2010 at 5:24 am

#226583

Hi guys,
I want different tables to be updated based on different conditions.
Can I use CASE and update different tables as per condition ?
Eg : If a variable value is 1, it should update TABLE A, If 2, TABLE B , etc
Can someone help ?
Sanuj

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76987 More actions · Answer 1

No. You can only update one table per UPDATE statement. You can simulate this using an INSTEAD OF trigger, but it's probably easier to just do this in your main process.

What were you hoping to gain by doing this in a single statement?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sunitkrishna SSCrazy Points: 2233 More actions · Answer 2

If you are insisting,you can use case statement.Consider the following example:

---------------------------------------------------------------------

create table #tblGuest (Guestid integer ,Fname varchar(20),Lname varchar(20),Date datetime,employeeid integer)

create table #tblEmployee (employeeid integer ,Fname varchar(20),Lname varchar(20),employeecode varchar(30))

insert into #tblGuest values(1,'manu','sankar','12 dec 2009',112)

insert into #tblEmployee values(1,'jay','','e123')

select * from #tblGuest

select * from #tblEmployee

declare @chk int

declare @STR varchar(200)

set @chk=1

set @STR='update '+case(@chk)when 1 then '#tblGuest set fname=''manu'' where Guestid=1'

when 2 then '#tblEmployee set set fname=''jaya'' where employeeid=1'

end

select @STR Query

----

execute (@str)

select * from #tblGuest

select * from #tblEmployee

drop table #tblGuest

drop table #tblEmployee

---
Thinking is the hardest work there is, which is the probable reason so few engage in it.

Sunitha
😎

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 3

Hi Pals,

Thanks for the reply... I did it using dynamic SQL . but tiresome work 🙁

Best Regards,

Sanuj

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 4

Hi Friend,

I have a situation where udpation happens in different tables according to the input parameter value. I thought of finding some shortcut to do it in one shot.

Anyway, Thanks for the help

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

I'd personally use a proc with a bunch of IF/ELSE IF, if I had to do this. Pretty? No. Easy to maintain? No. Best way I've found? Sadly, yes.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA