How to make multiple select statements into a SQL VIEW and MODIFY DATA THROUGH A VIEW?

Question

I have these multiple select statements that I would like to make a

CREATE VIEW dbo.vw_customers_addresses_services_servicetanks

Here's my SELECT STATEMENTS:

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)

which is working as a multi select statement now. However my goal is to MODIFY DATA THROUGH A SQL VIEW where I can just do a quick UPDATE on the AddressID RESULT from the third select statement using SSMS and the magic of EDIT TOP X ROWS.

When I place my CREATE VIEW above the first select statement, it gives a big red squiggley with "Incorrect syntax: 'CREATE VIEW' must be the only statement in the batch."

Am I dreaming or can this be done as I described?

score 0 · Answer 1 · answered Feb 24 '18 at 20:19

The docs... https://learn.microsoft.com/en-us/sql/relational-databases/views/modify-data-through-a-view

A codes sample for how to modify data throug a view that has many underlying select statements (tables).

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

How to make multiple select statements into a SQL VIEW and MODIFY DATA THROUGH A VIEW?

1 Answers1