[Koha-devel] Something I learned about MySQL today

[Michael Hafen]

I thought I'd share something I learned this morning about MySQL.
The topic is using information in a table in the same query that is
inserting that information into the table.  Whenever I forget that MySQL
doesn't like that it trips me up.  I saw it happen recently in a Koha
database update, so it was on my mind.
Today I found out that it can be done if table aliases are used in the
subquery.  I was working on an entirely different software when it tripped
me up again, but I decided to see if there was a way after all.
Here's the query I ended up with, that works in recent versions of MySQL,
as an example:

insert into users
(group_id,handle,name,email,user_notify,password,security_level,landing_page,ldap_username,restricted_leaderboard,search,ticketSelectLimit)
(select 33,'user2',u2.name,u2.email,u2.user_notify,u2.password,u2.security_level,u2.landing_page,h2.ldap_username,h2.restricted_leaderboard,h2.search,h2.ticketSelectLimit
from users as u2 where handle = 'user' );


-- 
Michael Hafen
Washington County School District Technology Department
Systems Analyst
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20230221/9206cbc2/attachment.htm>