I have two tables auth_user
and temp
These are their schema:
CREATE TABLE "auth_user" (
"id" integer NOT NULL PRIMARY KEY,
"username" varchar(30) NOT NULL UNIQUE,
"first_name" varchar(30) NOT NULL,
"last_name" varchar(30) NOT NULL,
"email" varchar(75) NOT NULL,
"password" varchar(128) NOT NULL,
"is_staff" bool NOT NULL,
"is_active" bool NOT NULL,
"is_superuser" bool NOT NULL,
"last_login" datetime NOT NULL,
"date_joined" datetime NOT NULL
);
CREATE TABLE "temp" ( "id" integer, "username" varchar(30));
I want the id
fields in the auth_user
table to be updated to the id
field of the temp
table provided the username is the same. How can I achieve this?
I tried this SQL:
Update auth_user set id=(select temp.id from temp where temp.username=auth_user.username);
But I get this error:
Error: datatype mismatch
I found this question Update table values from another table with the same user name
It is similar to my question. Looking at the answer in that page, I tried this query
update auth_user set id=(select temp.id from temp where temp.username=auth_user.username) where exists (select * from temp where temp.username=auth_user.username);
and it works great. Same as my query in the question but with just an extra where
clause. I don't get the Error: datatype mismatch
now (but I don't know the exact reason why).