如何将多个选择语句制作到SQL视图中，并通过视图修改数据

Question

我有这些我想制作

的多个选择语句

CREATE VIEW dbo.vw_customers_addresses_services_servicetanks

这是我的选择语句：

SELECT * FROM Customers
     WHERE EXISTS
      (SELECT * FROM Addresses
       WHERE Addresses.OwnerId = Customers.Id
       AND Addresses.OwnerId = 97587
       AND Addresses.TenantId = 1013);

(SELECT * FROM Addresses
     WHERE Addresses.OwnerId = 97587
     AND Addresses.TenantId = 1013
     AND Addresses.Type = 'Delivery');

SELECT * FROM dbo.Services
     WHERE EXISTS
      (SELECT * FROM Addresses
       WHERE Addresses.Id = Services.AddressId
       AND Addresses.OwnerId = 97587
       AND Addresses.TenantId = 1013);
(SELECT ServiceTanks.*
     FROM ServiceTanks, Addresses, Services
      WHERE Services.Id = ServiceTanks.ServiceId
      AND Addresses.Id = Services.AddressId
      AND Addresses.OwnerId = 97587
      AND Addresses.Tenantid = 1013)

现在可以用作多选择语句。但是，我的目标是通过SQL视图修改数据，在该视图中，我可以使用SSMS从第三个选择语句和编辑top x行的魔术进行快速更新。

。

当我将创建视图放置在第一个选择语句的上方时，它给出了一个带有"不正确语法：'创建视图'的大红色Squiggley，必须是批处理中的唯一语句。"

我是在做梦还是可以像我描述的那样完成？

Answer 1

文档...https://learn.microsoft.com/en-us/sql/relational-databases/views/modify-data-tha--though-a-a-view

一个代码示例，用于通过具有许多基础选择语句(表(的视图来修改数据。

--tables
if exists (select * from sys.tables where name = 'table1')
    drop table table1
go
if exists (select * from sys.tables where name = 'table2')
    drop table table2
go
create table table1 (tkey1 int identity(1,1), fk int, name varchar(32));
create table table2 (tkey2 int identity(1,1), name varchar(32));
--data
insert into table2 (name) values ('MS')
insert into table2 (name) values ('OSS')
insert into table2 (name) values ('Oracle')
insert into table1 (fk, name) values (3, 'SQL')
insert into table1 (fk, name) values (1, 'postgreSQL')
insert into table1 (fk, name) values (2, 'mySQL')
go
if exists (select * from sys.views where name ='vw')
    drop view vw
go
create view vw
as
select t1.tkey1, t1.fk, t1.name as t1_name, t2.tkey2, t2.name as t2_name from table1 t1 inner join table2 t2 on t1.fk = t2.tkey2
go
select * from vw
go
begin tran
update vw set fk = 2 where fk = 1
update vw set fk = 1 where fk = 3
update vw set fk = 3 where fk = 2
select * from vw
rollback tran
begin tran
update vw set t2_name = 'OSS' where fk = 1
update vw set t2_name = 'MS' where fk = 3
update vw set t2_name = 'Oracle' where fk = 2
select * from vw
commit tran