Editable Views...

[Jim R]

I've created a number of views that were editable.  I reconfigured how I handled one aspect and had to redo some of my views.  They are now uneditable.  

Specifically, two columns (grouping and rankPos) are updated a lot.  Occasionally s.id is too. 

What about this structure would make it uneditable?

 

select 
p.id as pid,
p.grouping,
p.position,
p.rankClass,
p.rankPos,
p.nameFirst,
p.nameLast,
p.feet,
p.inches,
p.level,
p.grade,
school_name,

s.id as sid,
aau_team

from a_players p

JOIN a_schools s
	ON p.schoolID = s.id
JOIN a_aau a
	ON p.id = a.playerID
JOIN a_aau_teams t
	ON t.id = a.aau_team_id

WHERE grade = 26 && position = 5 && grouping IS NOT NULL
	
ORDER BY grouping desc,-rankPos desc,nameLast

 

Edited May 15, 2023 by Jim R

[kicken]

You can try reviewing the documentation on updatable views and see if you can spot a reason.

[Jim R]

Just now, kicken said:

You can try reviewing the documentation on updatable views and see if you can spot a reason.

I already did.  That's why I came here to ask my question.  

[kicken]

I don't typically use updatable views, so I'm not sure.  I don't see anything off hand.  Is the query referencing other views, or only real tables?  Does the query return multiple rows for the same p.id value?

[Jim R]

7 hours ago, kicken said:

I don't typically use updatable views, so I'm not sure.  I don't see anything off hand.  Is the query referencing other views, or only real tables?  Does the query return multiple rows for the same p.id value?

I don't see anything off hand either.  It doesn't query other Views, just real tables.  It doesn't have repeated columns, requiring aliasing other than id.  I had read that page you linked a couple of times throughout the day.  I use this version for 20 different views, 5 positions across the 4 different grades, and I edit grouping and rankPos a great amount as they progress through high school.  

This views had worked before, but I added the joins to a_aau and a_aau_teams, changing how I derived which summer/travel/AAU basketball team they play for.  It broke the previous view, which I figured it would.  

When I use the query to create the View.  I can edit the query results.  (I use Sequel Pro.)

[Jim R]

(Disclosure:  These kids have all had their names published in game programs for the public to view.)

The error is noted in the picture.  "Field has no or multiple table or database."  

I would get it if it didn't let me edit the columns I used to join the tables, but I'm not.  I can't edit any of them.  In my other views, I can.  

[kicken]

If you post the structures of your tables, I can look into it more.

[Jim R]

In order they should be...

 

• the view 
• a_aau
• a_aau_teams
• a_players
• a_schools

 

[kicken]

Screen shots are not useful.  I can't import a screenshot into Mysql to work on it.  Post the CREATE TABLE statements in a code block so they can be copy/pasted into mysql.

[Jim R]

The View in question:

CREATE ALGORITHM=UNDEFINED DEFINER=`courtana`@`%` SQL SECURITY DEFINER VIEW `_26_5`
AS SELECT
   `p`.`id` AS `pid`,
   `p`.`grouping` AS `grouping`,
   `p`.`position` AS `position`,
   `p`.`rankClass` AS `rankClass`,
   `p`.`rankPos` AS `rankPos`,
   `p`.`nameFirst` AS `nameFirst`,
   `p`.`nameLast` AS `nameLast`,
   `p`.`feet` AS `feet`,
   `p`.`inches` AS `inches`,
   `p`.`level` AS `level`,
   `p`.`grade` AS `grade`,
   `s`.`school_name` AS `school_name`,
   `s`.`id` AS `sid`,
   `t`.`aau_team` AS `aau_team`
FROM (((`a_players` `p` join `a_schools` `s` on((`p`.`schoolID` = `s`.`id`))) join `a_aau` `a` on((`p`.`id` = `a`.`playerID`))) join `a_aau_teams` `t` on((`t`.`id` = `a`.`aau_team_id`))) where ((`p`.`grade` = '26') and (`p`.`position` = '5') and (`p`.`grouping` is not null)) order by `p`.`grouping` desc,-(`p`.`rankPos`) desc,`p`.`nameLast`;

a_aau table

CREATE TABLE `a_aau` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `playerID` int(2) DEFAULT NULL,
  `aau_uniform` int(3) DEFAULT NULL,
  `aau_team_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Team/Player` (`aau_team_id`,`playerID`)
) ENGINE=InnoDB AUTO_INCREMENT=360 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

a_aau_teams

CREATE TABLE `a_aau_teams` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `aau_program_id` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `aau_team` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `aau_year` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

a_players

CREATE TABLE `a_players` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `grouping` int(1) DEFAULT NULL,
  `position` int(1) DEFAULT NULL,
  `nameFirst` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nameLast` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `feet` int(1) DEFAULT NULL,
  `inches` int(2) DEFAULT NULL,
  `schoolID` int(30) DEFAULT NULL,
  `grade` int(4) DEFAULT NULL,
  `key_player` int(2) DEFAULT NULL,
  `top_player` int(2) DEFAULT NULL,
  `varsity` int(1) DEFAULT NULL,
  `level` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rankClass` int(2) DEFAULT NULL,
  `comment` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `devPos` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rankPos` int(3) DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `Duplicate Player` (`nameFirst`,`nameLast`,`schoolID`)
) ENGINE=MyISAM AUTO_INCREMENT=2283 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

a_schools

CREATE TABLE `a_schools` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gym` int(1) DEFAULT NULL,
  `toggle` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `region` int(4) DEFAULT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `school` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `school_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=445 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

[kicken]

The ORDER By clause in your view was preventing my update attempt from working.  Removing it allowed the update query to function.  Did your queries have the order by clause before?  Have you tried running an update query, or just going by the error your GUI tool is showing?

 

Likely not related to the immediate problem, but you have a type mismatch in your DB.  a_aau.aau_team_id is a VARCHAR being joined tot a_aau_teams.id which is an INT.  The types should be matching, so you likely want to update your VARCHAR to be an INT as well.

[Jim R]

1 hour ago, kicken said:

The ORDER By clause in your view was preventing my update attempt from working.  Removing it allowed the update query to function.  Did your queries have the order by clause before?  Have you tried running an update query, or just going by the error your GUI tool is showing?

Any thoughts on which part of the order by?  Because it's pretty essential to the view.  IIRC, that's the same Order By I've had the whole time.  The only thing that changed was joining the two aau_ tables.  

1 hour ago, kicken said:

Likely not related to the immediate problem, but you have a type mismatch in your DB.  a_aau.aau_team_id is a VARCHAR being joined tot a_aau_teams.id which is an INT.  The types should be matching, so you likely want to update your VARCHAR to be an INT as well.

I forgot to change that.  I really gutted how I was handling players' summer teams.  I wanted more flexibility to create content.  

[kicken]

2 hours ago, Jim R said:

Any thoughts on which part of the order by

I believe it's just the order by being there at all.  With join's in the view, your update is a multi-table update, which the documentation for update says:

Quote

For multiple-table syntax, ORDER BY and LIMIT cannot be used.

If your view had no joins before then it would have probably worked but now with joins it will not.

 

I'm in the habit of not including an ORDER BY clause in my views at all.   I mostly use SQL Server which doesn't allow you to do so unless you apply a limit to the result set as well.  The ORDER BY clause is generally intended to only exist in whatever the final query is, so you'd do:

select whatever 
from yourView 
order by whatever

rather than have the order by as part of the view.

[Jim R]

29 minutes ago, kicken said:

If your view had no joins before then it would have probably worked but now with joins it will not.

I did have Order By with Joins in these views before, and I have other views, that definitely have Order By with Joins, which I can edit.  I didn't see anything in the documentation you linked that spoke of Order By and Limit.   I saw a reference to "certain types of joins", but it I didn't see the information on it.  

[Jim R]

Example:

This view is allowing me to edit:

CREATE ALGORITHM=UNDEFINED DEFINER=`courtana_jwr`@`47.227.210.171` SQL SECURITY DEFINER VIEW `_offers`
AS SELECT
   `p`.`id` AS `pid`,
   `p`.`nameFirst` AS `nameFirst`,
   `p`.`nameLast` AS `nameLast`,
   `p`.`grade` AS `grade`,
   `s`.`city` AS `city`,
   `s`.`school` AS `school`,
   `c`.`college` AS `college`,
   `c`.`level` AS `level`,
   `po`.`recruit_type` AS `recruit_type`,
   `po`.`time` AS `time`
FROM (((`a_players_offers` `po` left join `a_players` `p` on((`po`.`playerID` = `p`.`id`))) left join `a_schools` `s` on((`p`.`schoolID` = `s`.`id`))) join `a_colleges` `c` on((`po`.`collegeID` = `c`.`id`))) order by `po`.`time` desc;

 

 

That's with two left joins and two order by criteria.   I've tried the other View with left joins, still won't edit.  So what's the difference?

Edited May 16, 2023 by Jim R

[kicken]

27 minutes ago, Jim R said:

This view is allowing me to edit

Not me, if I try and fill in the missing tables.

Quote

Error Code: 1288. The target table _offers of the UPDATE is not updatable

Since I'm not all that well versed in this area, I don't really have much insight to provide without trying to setup some more complete test db.  My general advice would be to not use the views for your updates.  At least, not directly.  You could probably still use the view to filter, but update the target table directly.  For example, this fails with the error above:

update _offers o
set grade=5
where level=?

But this works:

update a_players p
join _offers o on o.pid=p.id
set p.grade=5
where o.level=?

Otherwise maybe someone else with more mysql & view experience will chime in.

[Jim R]

1 hour ago, kicken said:

Since I'm not all that well versed in this area, I don't really have much insight to provide without trying to setup some more complete test db.  My general advice would be to not use the views for your updates.  At least, not directly.  You could probably still use the view to filter, but update the target table directly.  For example, this fails with the error above:

Appreciate it, but that's not feasible for my needs.  I don't update multiple players to the same value all that often, never in the rankPos column, rarely in a bulk way for any other column.  That was the other nice thing I had.  When I'd change rankPos for a player, the View would reload, so my rankings were always in order.  

 

Hopefully someone else will wander and take a look.  

 

 

[Jim R]

Soooo...even using an alias, such p.id as pid and s.id as sid caused it to be uneditable because I had two columns with the same name.  

 

Makes sense regardless, since changing s.id would create issues because I wouldn't be interested in changing the id of that field in a_schools.  Rather use schoolID on the a_players table when a player changes schools.  

 

I do appreciate your time.  90% of time you guys help directly.  The other 10%, you bring new ideas to the table for me check...and I get lucky.