CODEWITHSUNDEEP
mysqlsql
user3574776
user3574776

Reputation:

MySQL - Problem Updating table with values from a second table

Background

I'm working on a DB that stores information about people in my town. The main table Citizens has all the info, but lacks the DNI row (think of it as the "NIF", an integer number unique for every person), and so I was tasked with updating that table with the DNI values from another one (New_Data). The challenge is to update the Citizens with the DNI field from New_Data.

Both table share the id field, which is formed by the full name + year of birth. I don't have permission to change the structure of any of these tables.

I'm using HeidiSQL to run the queries.

Table structure (simplified)

Citizens:

id full_name dni
Carlos Juan Fernandez1976 Carlos Juan Fernandez NULL
Daniela Lucia Kaltz2001 Daniela Lucia Kaltz NULL

New_Data:

id dni
Carlos Juan Fernandez1976 21.123.123
Daniela Lucia Kaltz2001 40.214.123

The Problem

I've tried different queries from all over the internet and cannot for the life of my get it to work. Some examples include:

Query #1

UPDATE Citizens AS c
INNER JOIN New_Data AS n ON (c.id = n.id)
SET c.dni = n.dni;

Goes on an infinite loop until I stop it. Doesn't update any row.

Query #2

UPDATE Citizens AS c SET dni = New_Data.dni WHERE c.id = New_Data.id;

Throws error: "Unknown column New_data.dni"

Query #3

UPDATE Citizens, New_Data
SET Citizens.dni = New_Data.dni
WHERE Citizens.id = New_Data.id;

Similar to the second one but including New_Data in the UPDATE clause. Also goes on an infinite loop without results.

Query #4

UPDATE Citizens c
JOIN New_Data n ON c.id = n.id
set c.dni = n.dni WHERE c.id = n.id

Again: infinite loop.

Funny stuff

If I run:

SELECT n.dni, c.full_name FROM New_Data AS n
INNER JOIN Citizens AS c ON (c.id = n.id);

I can see the DNI and full names of all citizens, but the same INNER JOIN doesn't work with the UPDATE clause.

Upvotes: 1

Views: 67

Answers (2)

DamPlz
DamPlz

Reputation: 130

It seems your MySql session has the safe-updates option set.

In this case, you can't update (nor delete) records without specifying a key (ex. primary key) in the where clause. You can try:

SET SQL_SAFE_UPDATES = 0;

Or you can modify your query to follow the rule (use primary key in where clause).

I reproduced and tried your first query and it works.

EDIT for the "SQL (1205): Lock wait timeout exceeded;" error:

This can be caused because one query is locking one or more tables. You can run the command:

SHOW FULL PROCESSLIST;

There you should see a table like this (depending your interface). In column 'state' you can see which processes are locked, and using the column id run:

kill [ID];

After that, your tables should be unlocked and your update query should run properly.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Try indexing. For this query:

UPDATE Citizens c JOIN
       New_Data n
       ON c.id = n.id
    SET c.dni = n.dni;

You want an index on New_Data(id, dni).

Upvotes: 2

Related Questions