SQL View Update

Hello!
I am using phpmyadmin in order to create view with SQL , my problem is that I want to update values in view without affecting the original table (the one that created the view)

Example

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'
UPDATE vwCustomersParis
SET City = 'Lyons'

Thanks in advance

i’m sorry, what you are asking for is not possible – a view is nothing more than a stored query, and you can’t update a query

i’m not even sure a materialized view will help you

perhaps what you need is just a second table which you can populate from the first, and then update separately

Are… you trying to update the ‘values’ in the view… or are you trying to redefine the view to change its where clause?

I am trying to update values

What you’re trying to say then is that you’ve put a window into a house, and now you want to draw on the window to change the color of the rug.

Makes no sense.

Create a “generic” view:

CREATE VIEW Customers
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers

Use the view:

SELECT * FROM Customers 
WHERE City = 'Paris'

or

SELECT * FROM Customers 
WHERE City = 'Lyon'

No need for update the view IMHO.

Or you can create a stored procedure and pass Paris or Lyon…

1 Like

I’m far from being a database expert, but I think of Views as “virtual tables”. For example, if I have a table with say 20 fields but I frequently query only 3 of those fields or I’m often only interested in rows when certain conditions are met, I might create a View as a kind of “mini table” to make things easier for me without creating another “real but smaller table” that contains some of the same as the larger table.

I’m guessing you don’t want an UPDATE, but CASE (or maybe IF) could do what you’re wanting to do. * untested, but something like eg.

SELECT 
  CASE City 
    WHEN 'Paris' THEN 'Lyons' 
  END AS 'changed_city_name'

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.