chcialem jeszcze jedno pytanie, zeby nie zakladac nowego tematu.
Mam 3 tabele:
CREATE TABLE Passenger
(passenger_no VARCHAR(50),
f_name VARCHAR(10),
l_name VARCHAR(20),
age INT,
gender ENUM('M','F'),
CONSTRAINT pk_Passenger PRIMARY KEY(passenger_no)) ENGINE=InnoDB;
CREATE TABLE Booking
(id INT NOT NULL AUTO_INCREMENT,
seat_reserved INT,
date DATE,
booking_price INT,
booking_status ENUM('Confirmed','Not confirmed') DEFAULT 'Not confirmed',
booking_no VARCHAR(50),
phone_number INT,
email VARCHAR(20),
age_category ENUM('A','Y','I'),
flight INT,
passenger VARCHAR(50),
payment VARCHAR(50),
CONSTRAINT pk_Booking PRIMARY KEY(id)) ENGINE=InnoDB;
CREATE TABLE Payments
(payment_no VARCHAR(50),
amount INT,
card_expiry_month INT,
card_expiry_year INT,
card_no INT,
card_holder VARCHAR(20),
date DATE,
CONSTRAINT pk_Payments PRIMARY KEY(id)) ENGINE=InnoDB;
do wypelnienia Booking i Passenger uzywam procedury:
DROP PROCEDURE IF EXISTS add_booking;
delimiter //
CREATE PROCEDURE add_booking
(IN p_f_name VARCHAR(10), IN p_l_name VARCHAR(20), IN p_age INT, IN p_gender ENUM('M','F'),
IN p_seat_reserved INT, IN p_date DATE, IN p_phone_number INT, IN p_email VARCHAR(20), IN p_age_category ENUM('A','Y','I'), IN p_flight INT)
BEGIN
Declare Code varchar(50);
SELECT UUID() into Code;
Insert into Passenger (passenger_no, f_name, l_name, age, gender)
Values
(
Code, p_f_name, p_l_name, p_age, p_gender
);
Insert into Booking (seat_reserved, date, phone_number, email, age_category, flight, passenger)
Values
(
p_seat_reserved, p_date, p_phone_number, p_email, p_age_category, p_flight, Code
);
END;
//
delimiter ;
a do Payment takiej:
DROP PROCEDURE IF EXISTS `add_payments` ;
delimiter //
CREATE PROCEDURE add_payments
(
nazwisko VARCHAR(20), p_card_expiry_month INT,p_card_expiry_year INT,p_card_no INT,p_card_holder VARCHAR(20),p_date DATE
)
BEGIN
INSERT INTO Payments (card_expiry_month,card_expiry_year,card_no,card_holder,date)
VALUES (p_card_expiry_month,p_card_expiry_year,p_card_no,p_card_holder,p_date);
UPDATE Booking b, Payments s,Passenger p
SET payment_no = passenger_no
where p.l_name LIKE concat(nazwisko, '%');
END;
//
delimiter ;
Chodzi mi o to, zeby tabele Passenger i Payment mialy taki sam numer w passenger_no i payment_no. Chcialem sprawdzic jaki numer ma osoba o nazwisku podanym jako parametr i przypisanie, i to dziala, ale tylko za pierwszym razem, bo pozniej pisze ze zdublowalem klucz. W jaki sposob moge zmodyfikowac tego UPDATE'a ?
Bede bardzo wdzieczny za pomoc.