How to use mySQL view and UPDATE to change a table

128
April 23, 2017, at 01:44 AM

Almost a complete rookie with mySQL, but I will try to explain what I have done so far as clearly as possible.

So I created a view in mySQL using the following data. This returns back details of members who were born in the current month. I have named it birthdays_thismonth.

SELECT member.member_id,CONCAT(person.firstname,' ', person.surname) AS 'Member Name', 
FLOOR(DATEDIFF(CURRENT_DATE,member.registration_date)/365) AS 'Years Member For', person.DOB
FROM person
JOIN member ON member.person_id = person.person_id AND MONTH(DOB) = MONTH(CURRENT_DATE)

Now, using this data, I want to be able to update the members table with a column that is named "birthday_discount".

I am having trouble getting the query done, as it seems to not like the fact that I am trying to update something that the select query is pulling from.

update member SET member.birthday_discount = 'b'
WHERE member.member_id IN
(SELECT member.member_id FROM member
INNER JOIN birthday_thismonth ON member.member_id = birthday_thismonth.member_id)

It seems like I am trying to read from member too much, and it is getting confused, but I cannot work out a way to do it from research. Any pointers as to what I am doing wrong would be appreciated, as the individual queries work fine, just not when put together. Thank you.

Answer 1

I think you just want a correlated subquery or join:

update member m join
       birthday_thismonth btm
       on m.member_id = btm.member_id
    set m.birthday_discount = 'b';
READ ALSO
Loading data returns null for values present in xml file

Loading data returns null for values present in xml file

I'm trying to load an so dump file PostLinks xml file into Mysql tableThe data loads perfectly without any warnings but the fields of date and PostLinkTypeId remain null even though the values are present in the xml file

194
MYSQL before insert trigger not firing

MYSQL before insert trigger not firing

For some reason my database in MYSQL Workbench is allowing me to insert out-of-range data into the 'occupancy column', which it should not

143
num_rows returning 0 on a login form

num_rows returning 0 on a login form

I know this has been discussed but the problem is slightly confusingI am using mysqli prepared statements, but I also tried with raw queries

157