SQL command to update column value in table

Question

Following is code on python manage shell

>>> User.objects.filter(email__icontains="gmail.com").values_list("email", flat=True)
[u'[email protected]', u'[email protected]', u'[email protected]', u'[email protected]', u'[email protected]']
>>> for ii in User.objects.filter(email__icontains="gmail.com"):
...      ii.email = ii.email.replace("@gmail.com", "@custom.com")
...      ii.save() 
...     
... 
>>> User.objects.filter(email__icontains="gmail.com").values_list("email", flat=True)
[]
>>> User.objects.filter(email__icontains="@custom.com").values_list("email", flat=True)
[u'[email protected]', u'[email protected]', u'[email protected]', u'[email protected]', u'[email protected]']
>>>

I want to write SQL command in Postgresql terminal (python manage dbshell)

How can I convert above in SQL command?

Following are my try:

[Edited1]:

Get Target email ids by SQL command:

dp=# SELECT  email FROM auth_user where email LIKE '%@gmail.com';
           email           
---------------------------
 [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]
(5 rows)

dp=#

bakkal · Accepted Answer · 2015-09-07 12:05:24Z

3

How can I convert above in SQL command?

You can have a look at the query Django generates for this, it might not be runnable as in (e.g. missing params that are sent by Django), but it'll give you a good idea of how Django translates it to SQL

The idea is to print this value: Model.objects.filter(...).values_list(...).query

query = User.objects.filter(email__icontains="@custom.com").values_list("email", flat=True).query

# Make it print it
print query 
print(query) # Python 3 or with "from future import print_function"

edited Sep 7, 2015 at 12:05

answered Sep 7, 2015 at 11:22

bakkal

55.6k12 gold badges136 silver badges113 bronze badges

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

1 Comment

Vivek Sable Over a year ago

Thank you, Up-voted. I add my answer too can you review by answer?

jazgot · Accepted Answer · 2015-09-07 11:31:53Z

1

So you want to replace domain in emails, here is test select:

select email, replace(email, '@gmail.com', '@custom.com') as new_email 
from auth_user 
where email like '%@gmail.com';

And update will be:

update auth_user 
set email = replace(email, '@gmail.com', '@custom.com') 
where email like '%@gmail.com';

answered Sep 7, 2015 at 11:31

jazgot

2,07315 silver badges25 bronze badges

1 Comment

Vivek Sable Over a year ago

@Thank you, Up-voted. I add my answer too can you review my answer?

Vivek Sable · Accepted Answer · 2015-09-11 03:12:47Z

Following is my solution:

UPDATE auth_user Set email = replace(email, '@gmail.com', '@custom.com') where email LIKE '%@gmail.com';

Demo:

Go into dbshell 1. cd /var/op/project_name python manage dbshell

dp=# SELECT  email FROM auth_user where email LIKE '%@gmail.com';
           email           
---------------------------
 [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]
(5 rows)

dp=# SELECT  email FROM auth_user where email LIKE '%@custom.com';
 email 
-------
(0 rows)

dp=# UPDATE auth_user Set email = replace(email, '@gmail.com', '@custom.com') where email LIKE '%@gmail.com';
UPDATE 5

dp=# SELECT  email FROM auth_user where email LIKE '%@gmail.com';
 email 
-------
(0 rows)

dp=# SELECT  email FROM auth_user where email LIKE '%@custom.com';
           email            
----------------------------
 [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]
(5 rows)

dp=#

Collectives™ on Stack Overflow

SQL command to update column value in table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related