sqlite3 Error: datatype mismatch on complex update operation

Question

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

could you provide the sample data you used because i can't duplicate the error. — heretolearn, Aug 28 '12 at 15:05
Oh I didn't provide because it has sensitive information. Anyways I found the answer. — Vigneshwaran, Aug 29 '12 at 05:59

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

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).

edited May 23 '17 at 11:56

Community

1
1

answered Aug 29 '12 at 06:09

Vigneshwaran

3,265
6
23
36

1

looks like your inner query is returning a null value. – Sathyajith Bhat Aug 29 '12 at 09:38

sqlite3 Error: datatype mismatch on complex update operation

1 Answers1