Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update multiple tables in single query

Author  Topic 

smitagupta
Starting Member

1 Post

Posted - 2008-04-03 : 07:53:35
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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-03 : 07:56:23
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 - 2008-04-03 : 09:44:36
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-03 : 09:47:25
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

326 Posts

Posted - 2008-05-14 : 10:54:25
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 11:15:24
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

326 Posts

Posted - 2008-05-14 : 11:39:43
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

326 Posts

Posted - 2008-05-14 : 11:42:37
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 - 2008-05-14 : 11:46:37
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

30421 Posts

Posted - 2008-05-14 : 11:56:39
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

52326 Posts

Posted - 2008-05-14 : 12:04:40
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 - 2008-05-14 : 12:06:33
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

326 Posts

Posted - 2008-05-14 : 12:09:00
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

326 Posts

Posted - 2008-05-14 : 12:12:16
Brett,

What do you mean by predicate?

CardGunner
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-14 : 12:13:29
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

326 Posts

Posted - 2008-05-14 : 12:14:54
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

326 Posts

Posted - 2008-05-14 : 12:22:42
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

326 Posts

Posted - 2008-05-14 : 12:38:02
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
Go to Top of Page
   

- Advertisement -