2 Replies - 834 Views - Last Post: 06 August 2013 - 01:19 PM Rate Topic: -----

#1 toad87  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 188
  • Joined: 21-May 12

MySQL Error 1348 "Columns 'x' is not updatable"

Posted 05 August 2013 - 11:24 PM

I'm trying to update a row's `Time` column from a view.

I'm getting error 1348: "Columns 'Time' is not updatable".

I'm guessing it's because I used TIME_FORMAT when creating the `Time` column in my view.

If it is, how do I fix this? If it's not, why can't I update it?

Here's my create View code:
CREATE VIEW `AppointmentsView` AS
    select 
        `Appointments`.`appointment_number` AS `App. No.`,
        `Appointments`.`date` AS `Date`,
        time_format(`Appointments`.`time`, '%h:%i %p') AS `Time`,
        concat(`Customers`.`last_name`,
                ' ',
                `Customers`.`first_name`) AS `Customer`,
        `Jobs`.`description` AS `Job`,
        `Jobs`.`price` AS `Price`,
        `Appointments`.`tip` AS `Tip`,
        concat(`Employees`.`last_name`,
                ' ',
                `Employees`.`first_name`) AS `Employee`,
        `Appointments`.`status` AS `Status`
    from
        (((`Appointments`
        left join `Customers` ON ((`Appointments`.`customer_id` = `Customers`.`customer_id`)))
        left join `Employees` ON ((`Appointments`.`employee_id` = `Employees`.`employee_id`)))
        left join `Jobs` ON ((`Appointments`.`job_id` = `Jobs`.`job_id`)))
    order by `Appointments`.`date` , `Appointments`.`time`



UPDATE STATMENT:

UPDATE AppointmentsView
SET `Time` = '18:00:00'
WHERE `App. No.` = '2013090201';


This post has been edited by toad87: 05 August 2013 - 11:27 PM


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL Error 1348 "Columns 'x' is not updatable"

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: MySQL Error 1348 "Columns 'x' is not updatable"

Posted 06 August 2013 - 03:39 AM

Time isn't a value in a table! It's a value you've calculated with the function time_format. The view can't know how to map the value in to anything meaningful with an update statement.

Also, update through views are tricky. If they're complex enough, then the update becomes ambiguous and you can't do it. Because everything is an outer join here, you could probably update, if you had a valid column from Appointments.

Take the time_format out and give it a try.
Was This Post Helpful? 1
  • +
  • -

#3 toad87  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 188
  • Joined: 21-May 12

Re: MySQL Error 1348 "Columns 'x' is not updatable"

Posted 06 August 2013 - 01:19 PM

That's what I figured.

Thanks for confirming it baavagi.

I will lose the Time_Format and format the data on the client side instead.

Thanks

Toad
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1