New answers tagged

0

I believe you can use the aggregation pipeline for updates using the conditional operator. For example, change your update object to be an array like this: [ { $addFields: { "winning" : { $cond: { if: { $eq: [ "$fields.11", 1 ] }, then: 22, else: 33 } } } } ] This specifies that if the element 11 of the fields array equals to 1, then ...


3

There is no "first update", "second update" here, there is one statement, one atomic update. And this update produces 2 rows that are the same. If you want "the second" update to not succeed you should write 2 explicit updates, not one, inside 1 transaction. But in this case nolock would have no sense because the same transaction sees its own uncommitted ...


2

On the contrary .. I think your database server DOES read the updated values... to elaborate: simply visualize (if needed on a piece of paper), what happens during your update before (1,1),(1,2),(2,1),(2,2) 1st update -> (3,1),(1,2),(2,1),(2,2) 2nd update -> (3,1),(3,2),(2,1),(2,2) 3rd update -> (3,1),(3,2),(3 ***,1),(2,2) If I understand your ...


1

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling. PROCEDURE calling UPDATE for each JSON key Proof of concept to show what did not work in your attempt. CREATE PROCEDURE record_event ( foo_arg int, name_arg text, data_arg jsonb, occurred_at_arg timestamptz ) AS $...


2

Test select: select macaddress, upper(replace(macaddress,':','')) as new_macaddress from Macs; Update: update macs set macaddress = upper(replace(macaddress,':','')); Fiddle Test data: create table macs ( macaddress varchar(20) ); insert into macs values('90CCAADD3341'); insert into macs values('90:3f:ff:11:22:33'); insert into macs values('33:44:...


3

You might want one of these queries: #1 Extra column with window function: SELECT [GuidEmployee], [Date], [Hours], SUM([Hours]) OVER(PARTITION BY GuidEmployee,[Date]) as total_hours FROM [dbo].[Time]; Result GuidEmployee Date Hours total_hours 1 2012-03-02 00:00:00.000 2.00 6.50 1 ...


0

According the documentation MongoDB locks the database only when create an index in the foreground ( this option does not exists any more in MongoDB 4.2. ). On the other hand the collection are never exclusively locks. See more what locks are taken on MongoDB on What locks are taken by some common client operations?


1

Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended. Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if ...


0

The temp table is probably preferable. Parsing the same UPDATE statement one million times is going to get slow. Opening and closing transactions one million times can also be slow, but you can put all updates into a single transaction if you want. By having separate update statements you are basically forcing it to use a nested-loop plan, where the loop ...


0

I would think that Option 2 is more preferable because of the following: Less overall writes to the database (no extra table required) Less overall WAL activity (Option 1 involves both an INSERT/COPY and an UPDATE for each row involved) In either case, you could encounter some table bloat, in which case you'd want to make sure you VACUUM after doing this ...


0

Assuming that all records with wishlist IS NULL must be updated with the value taken from the closest previous (while sorting by id ASC) record with non-NULL wishlist value: UPDATE test SET wishlist = (@tmp := COALESCE(wishlist, @tmp)) WHERE (@tmp := -1) ORDER BY id ASC fiddle


0

You declared Postgres 10, for which McNets has provided a valid solution. It should be a bit more efficient without CTEs as those cannot be inlined before Postgres 12: UPDATE tbl SET data = ( SELECT jsonb_agg(jsonb_set(d, '{updatedAt}', '"15711500000"', false)) FROM jsonb_array_elements(data) d ); However, still pretty inefficient while not ...


1

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again. with ct as ( select id, jsonb_array_elements(data) dt from t ) , ct2 as ( select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2 from ct ) update t set data = (select jsonb_agg(dt2) ...


2

last part i want to get is either 3 or 4 characters. Ex: '1/2' or '1/12' UPDATE [TableName] SET [ColumnName] = LTRIM(RIGHT([ColumnName], 4))


1

There are a couple of options, depending on the state of your data. If the prefix is always 'aaa', then do this: UPDATE [TableName] SET [ColumnName] = LTRIM(RTRIM(REPLACE([ColumnName], 'aaa', ''))) If the prefix changes, but it is always the last 4 characters you want to retain, then do this: UPDATE [TableName] SET [ColumnName] = SUBSTRING([ColumnName], ...


Top 50 recent answers are included