diff options
| -rw-r--r-- | dbinit/demo.sql | 4 | ||||
| -rw-r--r-- | dbinit/procedures.sql | 16 |
2 files changed, 17 insertions, 3 deletions
diff --git a/dbinit/demo.sql b/dbinit/demo.sql index 56abc36..70d0116 100644 --- a/dbinit/demo.sql +++ b/dbinit/demo.sql @@ -33,8 +33,8 @@ insert into `formula1`.`specialposition` (`type`) values insert into `formula1`.`member` (`firstName`, `middleName`, `lastName`, `functionID`) values ("Mario", "", "Mario", 1), - ("Luigi", "", "Mario", 1), - ("Peach", "", "Toadstool", 1), + ("Carlos", "Sainz", "jr.", 1), + ("Max", "", "Verstappen", 1), ("Loek", "Le", "Blansch", 2); insert into `formula1`.`racedate` (`calendarID`, `raceTypeID`, `week`, `date`) values diff --git a/dbinit/procedures.sql b/dbinit/procedures.sql index 73627c2..d94ea65 100644 --- a/dbinit/procedures.sql +++ b/dbinit/procedures.sql @@ -18,6 +18,20 @@ create procedure spDeleteFlags() begin update `nationality` set `nationality`.`flag` = NULL - where `nationality`.`flag` is not NULL + where `nationality`.`flag` is not NULL; + end$$ +delimiter ; + +drop procedure if exists spUpdatePersons; + +delimiter $$ +create procedure spUpdatePersons(imgPath varchar(255)) + begin + select concat(imgPath, regexp_replace(concat(`firstName`, " ", `middleName`, " ", `lastName`), ' *', ' '), ".jpg") from `member`; + update `member` as `A` + set `A`.`photo` = ( + select load_file(concat(imgPath, regexp_replace(concat(`firstName`, " ", `middleName`, " ", `lastName`), ' *', ' '), ".jpg")) as `photo` + from `member` as `B` + where `B`.`ID` = `A`.`ID`); end$$ delimiter ; |