mySQL Inserting values randomly from another table

Question

I have a table "people" with ("ID","PassportNo")

+----------+----------+
| booking  | passport |
+----------+----------+
|        1 |   873246 |
|        1 |   535654 |
|       2  |   123232 |
+----------+----------+

CREATE TABLE people(
    id INTEGER,
    passportno INTEGER,
    PRIMARY KEY (id, passportno),
);

And I have a table passenger with ("ticketno","booking","passportno")

+-----------+---------+------------+
| thicketno | booking | passportno |
+-----------+---------+------------+
| 134133434 |       1 | NULL       |
| 324214234 |       1 | NULL       |
+-----------+---------+------------+ 


CREATE TABLE passenger(
ticketno INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
booking INTEGER,
passportno INTEGER,

How can I add all "passportno" from "people" table to "passenger" table based on the booking?

I believe it should be something like:

UPDATE passenger SET passportno = (SELECT passpotno FROM people where people.booking=passenger.booking

but I get an error "you are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"

UPDATE:

it works when I add SET SQL_SAFE_UPDATES = 0; ńow the problem is that I get 'subquery returns more than one row' and when I use a join I get the same passport number for anyone with the same booking id, I'd like to have all the different passport numbers.

Does this answer your question? [MySQL error code: 1175 during UPDATE in MySQL Workbench](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) — Alberto Moro, Dec 06 '19 at 15:42
Consider adding a column to "passenger" to number passports within a booking . In sql-sever one can do it on the fly, but in MySql i guess you need a column to persit the number. — Serg, Dec 06 '19 at 15:48
How can adding a column of passpoert numbers could be helpful? — Pedram, Dec 06 '19 at 15:59

score 0 · Answer 1 · answered Dec 06 '19 at 15:16

0

You can use a join:

UPDATE passenger p JOIN
       people pp
       on p.booking = pp.booking
    SET p.passportno = pp.passpotno;

answered Dec 06 '19 at 15:16

Gordon Linoff

1,242,037
58
646
786

it works when I add `SET SQL_SAFE_UPDATES = 0;` but in that case I will get the same passport number for anyone with the same booking id, I'd like to have all the different passport numbers. – Pedram Dec 06 '19 at 15:40

score 0 · Answer 2 · answered Dec 06 '19 at 15:44

0

As per this question you could for instance execute this command before updating your rows:

SET SQL_SAFE_UPDATES = 0;

answered Dec 06 '19 at 15:44

Francois Verollet

140
1
8

score 0 · Accepted Answer · answered Dec 06 '19 at 16:04

Your tables miss the key to uniquely match the rows Consider adding a column to "passenger" to number passports within a booking .

Demo MySql 8.0.

Data setup

create table people (booking int, passportno int);
insert people
values(1,873246)
,(1,535654)
,(2,123232);

create table passenger (thicketno int, booking int, passportno int, passpSeq int);
insert passenger
values (134133434, 1, NULL,NULL)
,(324214234, 1, NULL,NULL);

The query, set passpSeq first

UPDATE passenger
JOIN (SELECT @rank := 0) r
SET passpSeq=@rank:=@rank+1;

and now

UPDATE
  passenger p 
JOIN
  (  select booking, passportno, row_number() over(partition by booking order by passportno) rn
     from people  
  ) pp
       on p.booking = pp.booking and p.passpSeq=pp.rn
SET p.passportno = pp.passportno;

For older version you can emulate row_number() see http://www.mysqltutorial.org/mysql-row_number/

mySQL Inserting values randomly from another table

3 Answers3