Error updating 2 columns of different tables by means of a JOIN

Question

Error updating 2 columns of different tables by means of a JOIN

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have the following query with which I try to update the name of the user and the name of the post associated with that user

UPDATE users
    JOIN posts ON (users.id = posts.user_id)
SET
    users.nameUser = "mas blabla",
    posts.namePost = "blabla"
WHERE users.id = 1;

However, I get the following error again

But if I modify the query and leave it alone then it works:

UPDATE users
    JOIN posts ON (users.id = posts.user_id)
SET
    users.nameUser = "mas blabla"
WHERE users.id = 1;

I do not understand why the error

UPDATE

I have tried numeric values, other columns to update them and the query works; in this way

UPDATE users
    JOIN posts ON (users.id = posts.user_id)
SET
    users.nameUser = "mas blabla",
    posts.statusPost = 1
WHERE users.id = 1;

So why does a value in text string format fail?

I make the clarification, none of the titles of posts that I have registered coincide with the one of the update; no matter what value you put all the marks as duplicates

mysql sql

asked by element 12.11.2018 в 07:15

source

2 answers

1

For the error that you show: "Duplicate entry for key namePost", the namePost column of your posts table is UNIQUE and you already have a record with 'blabla', maybe that's why it does not let you update the column.

answered by 12.11.2018 в 07:36

SQLITE problem when creating a table with two Foreign Key Remove a character from a string in java

score 1 · Accepted Answer

Well answering my question, I explain the following

The error that says

duplicate entry ***** for posts

It's because I do not notice that I have more than one publication assigned or created by the same user, apart from that the field namePost has the attribute of UNIQUE then when I try to update the name of the post by means of a JOIN the same name tries to be assigned to those two records of the same user and therefore by the condition of UNIQUE in the name of the publication, is that that error comes out

SOLUTION

I added in WHERE the operator AND to set another parameter in the condition and that in this way the update does not happen in the way that it was and resulted in the error

UPDATE users
    JOIN posts ON (users.id = posts.user_id)
SET
    users.nameUser = "mas blabla",
    posts.namePost = "blabla"
WHERE users.id = 1 AND posts.namePost = "Aire comprimido";

So for example the UPDATE instead of trying to apply to both records of the same user, only applies to one