x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How can I update Multiple Tables at once

"CAN'T BE DONE" -- Crazy boy

Well at first glance, that may very well be the answer, as in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100207

Now, this might not be what the OP is looking for, but if you employ a partitioned view, then yes, it's doable

cut and paste the sample code to see it in action


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
*/

Print | posted on Thursday, April 03, 2008 9:49 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How can I update Multiple Tables at once

you can also use trigger...
4/14/2008 3:05 AM | Tal Olier
Gravatar

# re: How can I update Multiple Tables at once

hey what do u mean to say update multiple tables at once do u want to update the same thing into two tables
or different values in different tables
4/16/2008 5:22 AM | narayanan
Gravatar

# Meaning of Trigger,GO,AS,ON

Full Explaination of Trigger,ON,AS function,Droptable
4/16/2008 6:19 AM | Ashtu
Gravatar

#  How can I drop Multiple Tables at once

Please if u can help on drop on sql 2k
5/13/2008 9:45 AM | sanjana
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET