Jeremin Freed Meister Jeremin Freed Meister - 1 month ago 15
MySQL Question

MySQL copy values to another table

I need to copy Klient_ID values from table(Klient) to table(Kliendikaart). Command should choose ID by correct first name('Eesnimi'). I tried this command:

UPDATE Kliendikaart
SET Kliendikaart.Klient_ID = Klient.Klient_ID
FROM Kliendikaart
INNER JOIN Klient ON Kliendikaart.Eesnimi = Klient.Eesnimi;


but


You have an error in your SQL syntax; ... near 'FROM Kliendikaart
INNER JOIN Klient ON Kliendikaart.Eesnimi = Klient.Eesnimi' at line 1


Tables:

select * from Kliendikaart;
+-----------------+-----------+---------+----------+
| Kliendikaart_ID | Klient_ID | Eesnimi | Perenimi |
+-----------------+-----------+---------+----------+
| 1 | NULL | Priit | Triip |
| 2 | NULL | Tõnu | Lepp |
| 3 | NULL | Tiit | Kask |
| 4 | NULL | Linda | Orb |
| 5 | NULL | Salme | Kepp |
| 6 | NULL | Iiris | Toos |
| 7 | NULL | Kertu | Verb |
| 8 | NULL | Triinu | Kolk |
| 9 | NULL | Tõnis | Viip |
| 10 | NULL | Taavi | Kolk |
+-----------------+-----------+---------+----------+


and

select * from Klient;
+-----------+---------+----------+
| Klient_ID | Eesnimi | Perenimi |
+-----------+---------+----------+
| 1 | Priit | Triip |
| 2 | Tõnu | Lepp |
| 3 | Tiit | Kask |
| 4 | Linda | Orb |
| 5 | Salme | Kepp |
| 6 | Iiris | Toos |
| 7 | Kertu | Verb |
| 8 | Triinu | Kolk |
| 9 | Tõnis | Viip |
| 10 | Taavi | Kolk |
+-----------+---------+----------+


The output should look like this:

select * from Kliendikaart;
+-----------------+-----------+---------+----------+
| Kliendikaart_ID | Klient_ID | Eesnimi | Perenimi |
+-----------------+-----------+---------+----------+
| 1 | 1 | Priit | Triip |
| 2 | 2 | Tõnu | Lepp |
| 3 | 3 | Tiit | Kask |
| 4 | 4 | Linda | Orb |
| 5 | 5 | Salme | Kepp |
| 6 | 6 | Iiris | Toos |
| 7 | 7 | Kertu | Verb |
| 8 | 8 | Triinu | Kolk |
| 9 | 9 | Tõnis | Viip |
| 10 | 10 | Taavi | Kolk |
+-----------------+-----------+---------+----------+


Tables which I created are using these keys:

create table Klient(
Klient_ID int unsigned not null auto_increment,
Eesnimi varchar(30) not null,
Perenimi varchar(30) not null,
PRIMARY KEY (Klient_ID));

create table Kliendikaart(
Kliendikaart_ID int unsigned not null auto_increment,
Klient_ID integer unsigned,
Eesnimi varchar(30) not null,
Perenimi varchar(30) not null,
PRIMARY KEY (Kliendikaart_ID),
FOREIGN KEY (Klient_ID) references Klient(Klient_ID));

Answer

You should use

UPDATE Kliendikaart kd
INNER JOIN Klient k ON kd.Eesnimi = k.Eesnimi
SET kd.Klient_ID = k.Klient_ID;

Your query is for SQL Server, not for My SQL. And you should use alias for short and easy to read query.

Comments