Update Column from Columns within same table using SQLite

Question

I’m trying to update an email address value from users first- and lastname within the same table.

Pattern for generated Mail: <Firstname>.<Lastname>@test.org

Example Table “Persons”:

 Firstname  Lastname     email 

 Henley     Figueroa     [email protected]
 Samina     Morrison     [email protected] 
 Dev        Rowe         [email protected]

Wished result for table “Persons”:

Firstname Lastname    email 

Henley    Figueroa    [email protected]
Samina    Morrison    [email protected]
Dev       Rowe        [email protected]

SQL Code

UPDATE Persons
SET 
  email = (SELECT FirstName || "." || LastName ||"@"||"test.org" FROM Persons)

Actual result for table “Persons”:

Firstname   Lastname      email 

Henley      Figueroa      [email protected]
Samina      Morrison      [email protected]
Dev         Rowe          [email protected]

Only the first record of the returned result table is used over and over. Why?

If I ommit the SELECT:

UPDATE Persons
SET
    email = FirstName || "." || LastName ||"@"||"test.org"

I get the expected result.

Referring to your answers, I'm extending my question with an example of two tables. One with only names, the second for mail addresses.

Table Persons:

ID*	Firstname	Lastname
1	Henley	Figueroa
2	Samina	Morrison
3	Dev	Rowe

Table Addresses:

ID*	email
1	[email protected]
2	wrong@wrong,ng
3	[email protected]

UPDATE Addresses
SET email = (SELECT Firstname ||"."|| Lastname || "@test.org" 
             FROM Persons WHERE Persons.id = Addresses.id)

Demo

Here the UPDATE with SET and SELECT works (Every person gets a unique mail address). Shouldn’t I get the same problem here? From the output of SELECT I see that I get again (as expected) three records.

Gordon Linoff · Accepted Answer · 2021-01-31 18:26:35Z

2

You have the right syntax for your problem. So the question is why does this not do what you want?

UPDATE Persons
    SET email = (SELECT FirstName || "." || LastName ||"@"||"test.org" 
                 FROM Persons
                );

In fact, this should produce an error in any SQL engine -- although SQLite can be a bit lax about such errors. Why? The SET is expecting a single value. This code has a subquery that returns three values.

Note: The SQL Fiddle does show that SQLite accepts this syntax. Argggh! You can switch this to Postgres to see the error. With some modifications, to the create code you could also see the same error (subquery returns more than one row) using SQL Server or Oracle or just about any database.

Apparently, SQLite is just arbitrarily choosing one of those values -- the one that it first encounters. And this is the same value for all three rows.

You already know that a subquery is not correct here. You have the correct code in your question.

answered Jan 31, 2021 at 18:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Seven Over a year ago

Thanks for your answer, they gave me good hints and let me wondering again, so I extended my question. For me it’s still not clear why in my second example the subquery, which gets three records back works…

Gordon Linoff Over a year ago

@Seven . . . It is not clear to me either. I would consider this a bug. I'm guessing someone thinks this is a "feature" (and one not shared by other databases or the SQL standard). Why that would be I cannot really imagine. It is simple enough to add limit 1.

forpas · Accepted Answer · 2021-02-01 11:07:55Z

From Subquery Expressions:

A SELECT statement enclosed in parentheses is a subquery. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. The value of a subquery expression is the first row of the result from the enclosed SELECT statement. The value of a subquery expression is NULL if the enclosed SELECT statement returns no rows.

So the value you get by the subquery is the first row of the rows returned.

Of course, you don't need the SELECT statement.
This is a simple UPDATE statement that involves only the values of the columns of the current row:

UPDATE Persons
SET email = FirstName || '.' || LastName || '@' || 'test.org'

See the demo.
Results:

Firstname	Lastname	email
Henley	Figueroa	[email protected]
Samina	Morrison	[email protected]
Dev	Rowe	[email protected]

For the 2nd query with the 2 tables:

Shouldn’t I get the same problem here?

No, because the subquery is a correlated subquery, which returns for each row in the table Addresses only 1 row (provided there are no duplicate ids in Persons), the row which matches the condition Persons.id = Addresses.id.
If there is no row that matches the condition then it will return NULL.

Collectives™ on Stack Overflow

Update Column from Columns within same table using SQLite

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related