Solved

Update via Joined View

Posted on 2004-03-25
14
578 Views
Last Modified: 2012-06-21
I have never really used Views before in SQL and am wanting to know if I can do the following:

Table1
--------
CustomerID
OrderID
StatusDescription

Table2
---------
StatusID
StatusDescription

TableView3 (SELECT JOIN of Table1 & Table2)
--------------
CustomerID
OrderID
StatusDescription
StatusID

I want to be able to update the Table1 StatusDescription by updating the TableView3 StatusID ...

i.e. I have an SQL link table in MSAccess for TableView3, I type in "4" in the StatusID column of an existing record. "4" corresponds to "Shipped" in Table2. Table1 has the record updated with "Shipped" in the "StatusDescription" column.

Hope I was clear enough.

Thanks,

Pete.
0
Comment
Question by:Peter-Pan
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 1

Expert Comment

by:1markmc
ID: 10679521
You can update a view if the primary key for the underlying table for the field you are updating is included in the view.

If the XXXID fields above are primary keys (or in combination make the primary key) then updating view 3 should update the underlying table.  The modification cannot affect more than 1 of the base tables and the view mustbe created following the rules for updatabable views.  see BOL
0
 
LVL 13

Expert Comment

by:danblake
ID: 10679600
The modification cannot affect more than 1 of the base tables and the view mustbe created following the rules for updatabable views.
----------Correction:
If you want to be able to update one or more base tables, create an instead of delete/insert/update trigger on the view.
0
 
LVL 1

Author Comment

by:Peter-Pan
ID: 10679731
I am able to update the TableView3 easily for fields that belong to base Table1 ... I want to modify the record in base Table1 but by changing the "joined" value in the TableView3

So i guess you are saying the only way I can do this is via triggers???

I am off to do some research on Triggers as I have never done one. Do you have any suggestions on what the Trigger would look like for the above scenario?

Cheers.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:Peter-Pan
ID: 10679963
Just to clarify, here is example data:

Table1
--------
CustomerID  |  OrderID  |  StatusDescription
     5001       |      1       |  Being Processed  
     5012       |      2       |  Shipped              
     6456       |      3       |  Waiting Payment  
     6874       |      4       |  Payment Received
     4587       |      5       |  Shipped                

Table2
--------
StatusID |  StatusDescription
      1     |  Waiting Payment
      2     |  Payment Received
      3     |  Being Processed
      4     |  Shipped

TableView3
-------------
CustomerID  |  OrderID  |  StatusDescription  |  StatusID
     5001       |      1       |  Being Processed    |      3
     5012       |      2       |  Shipped                |      4
     6456       |      3       |  Waiting Payment   |      1
     6874       |      4       |  Payment Received |      2
     4587       |      5       |  Shipped                |      4

E.g. I would like to be able to change OrderID "1"s Status to "Shipped" by changing the StatusID column to a value of "4"
0
 
LVL 1

Expert Comment

by:1markmc
ID: 10680252
Yes, this is doable just by changing the view:  TableView3  

when you say:  

update tableview3 set statusID = 4 where OrderID = 1

The base table will change
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10680322
something like  this

"an instead of trigger"


create trigger Upd_tableview3 on tableview3
as instead of update

update table1
 set statusdescription = b.tabledescription
 from Table1 as A inner join Inserted as I
  on a.customerid=i.customerid
 inner join table2 as b
  on I.statusid = b.statusid
 Where i.statusid <> a.statusid

 
0
 
LVL 13

Expert Comment

by:danblake
ID: 10680742
I know this may make a very sore point here... .

why not design the two tables like so:
Table1
--------
CustomerID  |  OrderID  |  StatusID
     5001       |      1       |  3
     5012       |      2       |  4
     6456       |      3       |  1
     6874       |      4       |  2
     4587       |      5       |  4

Table2
--------
StatusID |  StatusDescription
      1     |  Waiting Payment
      2     |  Payment Received
      3     |  Being Processed
      4     |  Shipped

TableView3
-------------
CustomerID  |  OrderID  |  StatusDescription  |  StatusID
     5001       |      1       |  Being Processed    |      3
     5012       |      2       |  Shipped                |      4
     6456       |      3       |  Waiting Payment   |      1
     6874       |      4       |  Payment Received |      2
     4587       |      5       |  Shipped                |      4

?

Creating a FK Link between Table2 & Table 1 ?
Then if you update table1, it has to be one of the known values.

If a status description then changes it changes throughout table1 ?
And make the field StatusDescription a non-updatable field ?
(As the StatusDescription is very unlikely to change frequently)

The trigger would then become:
create trigger Upd_tableview3 on tableview3
as instead of update

update table1
 set statusid = b.statusid,
       customerid = i.customerid,
      orderid = i.orderid
 from Table1 as A
inner join Inserted as I
  on a.customerid=i.customerid
inner join table2 as b
  on I.StatusDescription = b.StatusDescription


A small step towards 3rd Normal form ?...
0
 
LVL 1

Author Comment

by:Peter-Pan
ID: 10681547
I agree with you danblake about the design of the tables, but the reason I am doing this is that this example is a simplified version of an existing scenario where several different sources such as web and MS Access frontends already write to an existing field with a description instead of an ID.

I will try the triggers in the morning. Thanks guys.


0
 
LVL 1

Author Comment

by:Peter-Pan
ID: 10685172
I have increased the points to 100 as you are continuing to help

Ok, so I am strill trying to get my head fully around the triggers .. not exactly sure what the a, i & b are for.

Below is the Trigger I am using on the real tables. My examples translate to the following tables:

Table1= CustomerOrders
Table2= ListOrderStatus
TableView3= CustomerOrderStatusID

CREATE TRIGGER Upd_CustomerOrderStatusID on CustomerOrderStatusID
AS INSTEAD OF UPDATE

UPDATE CustomerOrders
 SET OrderStatus = b.OrderStatus
 FROM CustomerOrders AS A INNER JOIN INSERTED AS I
  ON a.OrderID=i.OrderID
 INNER JOIN ListOrderStatus AS b
  ON I.OrderStatusID = b.OrderStatusID
 WHERE i.OrderStatusID <> a.OrderStatusID

I get the following error "Incorrect syntax near keyword 'AS'"

I am using MS SQL 2000, so it does support INSTEAD OF UPDATE

Thanks again ...
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 50 total points
ID: 10685314
sorry try moveing the as from before instead of update to after it..
;-)

CREATE TRIGGER Upd_CustomerOrderStatusID on CustomerOrderStatusID
 INSTEAD OF UPDATE
AS

UPDATE CustomerOrders
 SET OrderStatus = b.OrderStatus
 FROM CustomerOrders AS A INNER JOIN INSERTED AS I
  ON a.OrderID=i.OrderID
 INNER JOIN ListOrderStatus AS b
  ON I.OrderStatusID = b.OrderStatusID
 WHERE i.OrderStatusID <> a.OrderStatusID
0
 
LVL 13

Expert Comment

by:danblake
ID: 10685441
not exactly sure what the a, i & b are for.
Its table aliasing, so we do not have to reference the tablename every time we want to use a field.
(Very useful is using duplicate column names)
0
 
LVL 1

Author Comment

by:Peter-Pan
ID: 10685866
Closer .... Now I get told "Invalid column name 'OrderStatusID'"

Here are the fields from the real tables, my CREATE VIEW and the TRIGGER

CustomerOrders
************
[OrderID] [int] IDENTITY (5000, 1) NOT NULL
[CustomerID] [int]
[OrderStatus] [nvarchar] (32)

ListOrderStatus
***********
[OrderStatusID] [int] IDENTITY (1, 1) NOT NULL
[OrderStatus] [nvarchar] (48)

CustomerOrderStatusID
*****************
CREATE VIEW dbo.CustomerOrderStatusID
WITH  VIEW_METADATA
AS
SELECT     dbo.CustomerOrders.*, dbo.ListOrderStatus.OrderStatusID AS OrderStatusID
FROM         dbo.CustomerOrders INNER JOIN
                      dbo.ListOrderStatus ON dbo.CustomerOrders.OrderStatus = dbo.ListOrderStatus.OrderStatus

Trigger
*****
CREATE TRIGGER Upd_CustomerOrderStatusID on CustomerOrderStatusID
INSTEAD OF UPDATE AS

UPDATE CustomerOrders
 SET OrderStatus = b.OrderStatus
 FROM CustomerOrders AS A INNER JOIN INSERTED AS I
  ON a.OrderID=i.OrderID
 INNER JOIN ListOrderStatus AS b
  ON I.OrderStatusID = b.OrderStatusID
 WHERE i.OrderStatusID <> a.OrderStatusID
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 50 total points
ID: 10685949
There is no OrderStatusID in Aliased Table a CustomerOrders, the last line is the problem...
WHERE i.OrderStatusID <> a.OrderStatusID

You should be able to drop the last line, or change it to :
WHERE i.OrderStatus <> a.OrderStatus
0
 
LVL 1

Author Comment

by:Peter-Pan
ID: 10686233
Thanks guys got it working .. still only understand the update about 90% ... but will keep playing with it till I get to 100%.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question