sql-update

Django update queryset with annotation

by

For Django 1.11+ you can use Subquery: from django.db.models import OuterRef, Subquery, Sum Relation.objects.update( rating=Subquery( Relation.objects.filter( id=OuterRef(‘id’) ).annotate( total_rating=Sum(‘sign_relations__rating’) ).values(‘total_rating’)[:1] ) ) This code produce the same SQL code proposed by Tomasz Jakub Rup but with no use of RawSQL expression. The Django documentation warns against the use of RawSQL due to the possibility of … Read more

When running UPDATE … datetime = NOW(); will all rows updated have the same date/time?

by

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now “NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes as of … Read more

mongoose difference of findOneAndUpdate and update

by

Well there is the respective documentation to view for both .update() and .findAndModify() which is the root method of .findOneAndUpdate() here. But in the main differences there are: update(): Is meant to perform an atomic update operation against “one or more” documents matched by it’s query condition in a collection. It returns the number of … Read more

PostgreSQL rename attribute in jsonb field

by

In UPDATE use delete (-) and concatenate (||) operators, e.g.: create table example(id int primary key, js jsonb); insert into example values (1, ‘{“nme”: “test”}’), (2, ‘{“nme”: “second test”}’); update example set js = js – ‘nme’ || jsonb_build_object(‘name’, js->’nme’) where js ? ‘nme’ returning *; id | js —-+————————- 1 | {“name”: “test”} 2 … Read more