SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update multiple tables in single query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smitagupta
Starting Member

India
1 Posts

Posted - 04/03/2008 :  07:53:35  Show Profile  Reply with Quote
Hello All,

I want to update multiple tables using single query and fields name are same of tables.
I am trying like:

update tablename1 t1,tablename2 t2 set t1.fieldname1 = t2.fieldname1 = 'value' where condition;

or

update tablename1 t1,tablename2 t2 set t1.fieldname1 = 'value' t2.fieldname1 = value where condition;

Plzzzzzz help me.Thanx in advance.


Thanx & Regards,
Smita.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/03/2008 :  07:56:23  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
You can update one table at a time only.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/03/2008 :  09:44:36  Show Profile  Reply with Quote
au contraire



USE Northwind
GO

CREATE TABLE myTable99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1  AND 10), Col2 varchar(50))
CREATE TABLE myTable98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'x' UNION ALL
SELECT 2, 'y' UNION ALL
SELECT 3, 'z'

INSERT INTO myTable98(Col1, Col2)
SELECT 11, 'x' UNION ALL
SELECT 12, 'y' UNION ALL
SELECT 13, 'z'
GO

CREATE VIEW myView99
AS
SELECT Col1, Col2 FROM myTable99
UNION ALL
SELECT Col1, Col2 FROM myTable98
GO

SELECT * FROM myView99

UPDATE myView99 SET Col2 = 'x002548' WHERE Col2 = 'z'

SELECT * FROM myView99

/*
DROP VIEW myView99
DROP TABLE myTable99, myTable98
*/



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 04/03/2008 :  09:47:25  Show Profile  Reply with Quote
Found this trivia on the net a while ago

DECLARE @TableA TABLE (i INT)
DECLARE @TableB TABLE (i INT)

SELECT * FROM @TableA
SELECT * FROM @TableB

INSERT @TableA
OUTPUT inserted.i
INTO @TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT * FROM @TableA
SELECT * FROM @TableB


Jim
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  10:54:25  Show Profile  Reply with Quote
I'm trying to create a view but I get an error of incorrect keyword.

This is my first time creating view.

create view temp_mfg_unit_view
as

Declare @unit varchar(16)
set @unit='71054'

select 	t_cmnf, t_clot
from 	ttscfg200100 	--objects
where 	t_clot=@unit
union all 
select 	t_cmnf, t_clot
from 	terext401100	--rental contract lines
where 	t_clot=@unit


I didn't put all the tables in the code. There are about 15. As a select it works. I add the Create view... and it errors...Incorrect syntax near the keyword 'Declare'.

Can you do this type of statement when creating a view?

CardGunner

Edited by - cardgunner on 05/14/2008 10:56:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2008 :  11:15:24  Show Profile  Reply with Quote
quote:
Originally posted by cardgunner

I'm trying to create a view but I get an error of incorrect keyword.

This is my first time creating view.

create view temp_mfg_unit_view
as

Declare @unit varchar(16)
set @unit='71054'

select 	t_cmnf, t_clot
from 	ttscfg200100 	--objects
where 	t_clot='71054'
union all 
select 	t_cmnf, t_clot
from 	terext401100	--rental contract lines
where 	t_clot='71054'


I didn't put all the tables in the code. There are about 15. As a select it works. I add the Create view... and it errors...Incorrect syntax near the keyword 'Declare'.

Can you do this type of statement when creating a view?

CardGunner


Put the value directly rather than using a variable
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  11:39:43  Show Profile  Reply with Quote
I was afraid of that.

I'll have to see if '71054' can be called as a parameter in VS .net 2003. So I can create a report that asks for the unit to be updated and then the value we are updating to.

In any case I did cahnge @unit with '71054' and scratched the declare.

I created the view. Worked perfectly

Tried to update the t_cmnf and got an error. I'm trying to reasearch it now.

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

any ideas?


CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  11:42:37  Show Profile  Reply with Quote
Sorry here is the update statemnt I used
update tmp_mfg_unit_2 
set t_cmnf='OTH'
where t_clot='71054'



CardGunner
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2008 :  11:46:37  Show Profile  Reply with Quote
You don't have to

Just update the view with a where clause



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 05/14/2008 :  11:56:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by jimf

Found this trivia on the net a while ago

DECLARE @TableA TABLE (i INT)
DECLARE @TableB TABLE (i INT)

SELECT * FROM @TableA
SELECT * FROM @TableB

INSERT @TableA
OUTPUT inserted.i
INTO @TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT * FROM @TableA
SELECT * FROM @TableB

More here
http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2008 :  12:04:40  Show Profile  Reply with Quote
quote:
Originally posted by cardgunner

I was afraid of that.

I'll have to see if '71054' can be called as a parameter in VS .net 2003. So I can create a report that asks for the unit to be updated and then the value we are updating to.

In any case I did cahnge @unit with '71054' and scratched the declare.

I created the view. Worked perfectly

Tried to update the t_cmnf and got an error. I'm trying to reasearch it now.

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

any ideas?


CardGunner


One way to deal with this is to create an INSTEAD OF UPDATE trigger on view which updates the base tables instead of updating the view directly.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2669779&SiteID=1
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2008 :  12:06:33  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by cardgunner

I was afraid of that.

I'll have to see if '71054' can be called as a parameter in VS .net 2003. So I can create a report that asks for the unit to be updated and then the value we are updating to.

In any case I did cahnge @unit with '71054' and scratched the declare.

I created the view. Worked perfectly

Tried to update the t_cmnf and got an error. I'm trying to reasearch it now.

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

any ideas?


CardGunner


One way to deal with this is to create an INSTEAD OF UPDATE trigger on view which updates the base tables instead of updating the view directly.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2669779&SiteID=1



No...just remove the predicate



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  12:09:00  Show Profile  Reply with Quote
quote:
Originally posted by X002548

You don't have to

Just update the view with a where clause




I'm not sure what you mean.

I deleted the declare and added '71054' to parts of the union.

now the view works perfectly.

I tried to update the tables in that view and I get the error.

And there is a where in the update statement.

Seeing all the records in the view where unit 71054 I tried the update without the where cause all the records was that unit

update tmp_mfg_unit_2
set t_cmnf='OTH'

and still the same error

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.




CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  12:12:16  Show Profile  Reply with Quote
Brett,

What do you mean by predicate?

CardGunner
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/14/2008 :  12:13:29  Show Profile  Reply with Quote
quote:
Originally posted by cardgunner

Brett,

What do you mean by predicate?

CardGunner



The WHERE Clause...that's called a predicate

It "Predicts" the result set



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  12:14:54  Show Profile  Reply with Quote
quote:
Originally posted by cardgunner

quote:
Originally posted by X002548

You don't have to

Just update the view with a where clause




I'm not sure what you mean.

I deleted the declare and added '71054' to parts of the union.

now the view works perfectly.

I tried to update the tables in that view and I get the error.

And there is a where in the update statement.

All the records in the view was of unit 71054 I tried the update without the where clause.

update tmp_mfg_unit_2
set t_cmnf='OTH'

and still the same error

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.


CardGunner



CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  12:22:42  Show Profile  Reply with Quote
To start from scratch this is what I have so far...

create view tmp_mfg_unit_2
as

select 	t_cmnf, t_clot
from 	ttscfg200120 	--objects
where 	t_clot='23332'
union all 
select 	t_cmnf, t_clot
from 	terext401120	--rental contract lines
where 	t_clot='23332'

update tmp_mfg_unit_2
set t_cmnf='OTH'

Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.


Like I said prevoisly this is a shortened list I have more then just the 2 tables to update.

CardGunner
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
326 Posts

Posted - 05/14/2008 :  12:38:02  Show Profile  Reply with Quote
I tried this and got a different error

create view tmp_mfg_unit_2
as

select 	t_cmnf, t_clot
from 	ttscfg200120 	--objects
union all 
select 	t_cmnf, t_clot
from 	terext401120	--rental contract lines

update tmp_mfg_unit_2
set t_cmnf='OTH'
where t_clot='23332'

Server: Msg 4440, Level 16, State 9, Line 1
UNION ALL view 'tmp_mfg_unit_2' is not updatable because a primary key was not found on table '[ttscfg200120]'.


I do not wish to do it this way because once I add all the tables in the create view my recordset could be over a million.

CardGunner

Edited by - cardgunner on 05/14/2008 12:48:38
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000