CODEWITHSUNDEEP
mysql
Aimad Majdou
Aimad Majdou

Reputation: 583

insert multiple rows from table to another with a value

I want to insert multiple rows from table to another table, the table I want to get rows from, I only want twho columns, but in the distination table I have three columns, the two first columns in the source table will be affected to the two columns in the distination table, and the third clumn in the distination table will be affected by a value.

Here is the values I want to select from the source table :

SELECT codeArt, qte FROM LigneBonEntrée
            WHERE numBon = NEW.num

and in the distination folder I have an insert as :

INSERT INTO LigneInterventaire(codeArt, qteInv, numInv) 
            VALUES (NEW.codeArt, NEW.qte, LAST_INSERT_ID());

If I tried this :

INSERT INTO LigneInterventaire(codeArt, qteInv, numInv) 
SELECT codeArt, qte FROM LigneBonEntrée
WHERE numBon = NEW.num

I'll need to insert the value LAST_INSERT_ID()

How can I do that ?

EDIT:

What I'm really trying to do is that I have a trigger on a table when this trigger is fired, I want to insert to another table which called Inventaire the current date, and of course the inserted primary key will be the value of LAST_INSERT_ID(), then I want to use this LAST_INSERT_ID() for the following query :

INSERT INTO LigneInterventaire(codeArt, qteInv, numInv) 
            SELECT codeArt, qte FROM LigneBonEntrée
            WHERE numBon = NEW.num;

the value of LAST_INSERT_ID() will be affected to the column numInv.

and the is the trigger I'm using:

DELIMITER //
CREATE TRIGGER `accepterLivraion` BEFORE UPDATE ON `BonEntrée`
FOR EACH ROW
BEGIN
    DECLARE id INTEGER;
    IF NEW.etat = 'Accepté' THEN
        IF verifierLivraison(OLD.num) = FALSE THEN
            SIGNAL sqlstate '45001' set message_text = "La livraison n'a pas respectée les termes de la commande de sortie.";
        ELSE
            INSERT INTO Inventaire(`date`) VALUES (DATE(NOW());
            SET id = LAST_INSERT_ID();
            INSERT INTO LigneInterventaire(codeArt, qteInv, numInv) 
            SELECT codeArt, qte FROM LigneBonEntrée
            WHERE numBon = NEW.num;
        END IF;
    END IF;
END//
DELIMITER ;

Upvotes: 0

Views: 2273

Answers (1)

peterm
peterm

Reputation: 92785

If I understand you correctly you already have your value in a variable id. Therefore just change 

SELECT codeArt, qte FROM LigneBonEntrée

to 

SELECT codeArt, qte, id FROM LigneBonEntrée
                     ^^

If your table LigneBonEntrée has a column id then change the name of your variable to which you assign LAST_INSERT_ID() or use an alias for table.

Upvotes: 3

Related Questions