17

From the docs, conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed ...


9

Another example CREATE TABLE T1 (C INT PRIMARY KEY) CREATE TABLE T2 (C INT PRIMARY KEY) INSERT INTO T1 OUTPUT inserted.* INTO T2 VALUES (1),(2),(3); The query SET STATISTICS IO ON; SELECT T1.C, COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C) THEN -1 END) FROM T1 OPTION (LOOP JOIN) Shows no reads against T2 at all. The ...


5

I'm not sure that your MCVE fully represents the problem that you have, but I'll answer the question as given. This question is about performance, so having just a few rows in the table won't cut it. I duplicated your sample data a million times for a total of 6 million rows for FeeTest and 3 million rows for FeeTestClient. Code to do that below: DROP TABLE ...


5

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition. One clear way of solving this is building the two result sets as CTEs (WITH queries): WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned FROM planning_constraints ...), p ...


5

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.


4

Try this select a.Date, a.Foo, b.Bar from TableA a LEFT JOIN TableB b ON a.Date = b.Date UNION select b.Date, a.Foo, b.Bar from TableB b LEFT JOIN TableA a ON a.Date = b.Date Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left ...


4

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference. WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as planning_day, sum(sot_allowed) AS minutes_allowed FROM planning_constraints WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:...


4

To answer your question COALESCE would be utterly pointless where you placed it to begin with. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the subquery. Related: Query optimization or missing indexes? But that's irrelevant, because if a subquery returns no row, then it cannot return any ...


4

I've been reading and re-reading the original question. What I have come to is this: Return records from tblProjecten where matches exist for parameters that have been supplied (are not null). Where the parameters are not supplied (or null) then assume all matches for that child table Effectively you're making your joins dynamic based on whether a ...


4

Strip the incorrect pair of parentheses: CREATE INDEX idx_test_c ON test_c(COALESCE(updt_dttm, load_dttm)); db<>fiddle here The way you had it was effectively indexing the composite value (updt_dttm, load_dttm), COALESCE not doing anything. Your second problem was revealed by the added table definition: CREATE TABLE test_c ( insrt_prcs_id bigint ...


3

Assuming hours are added to table in chronological order. If not, sort them before inserting them! First we need to relate each record to successive records. You say that is done by matching their hrs1 & hrs2, and making sure the initial record ends one day before the next starts. Your sample data only includes two records that are sequential, so I ...


3

Given an index exactly like (note Fee is the only included column): CREATE INDEX [IX dbo.FeeTest ClientId, Val, Val2 (Fee)] ON dbo.FeeTest ( ClientId, Val, Val2 ) INCLUDE ( Fee ); Two alternatives: 1. Cross apply This uses an inner loop join, a reduced number of comparisons, and avoids a sort: SELECT FTC.Id, FTC.[Name], Fee =...


3

If the two queries: SELECT * FROM foo f LEFT JOIN baz b ON f.x = b.y; and SELECT * FROM foo f LEFT JOIN (SELECT * FROM baz) b ON f.x = b.y; return different results, then it's a bug and the table and view definitions are irrelevant. That should never happen. Before you go and post a bug report, check your view and queries in the version ...


3

The actual standard says that all of the WHEN clauses (as well as the ELSE clause) have to be parsed to determine the data type of the expression as a whole. I'd really have to get out some of my old notes to determine how an error is handled. But just off hand, 1/0 uses integers, so I would assume that while it's an error. It's an error with the integer ...


3

More importantly, if ... some of the results have no entries You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect results. Detailed explanation: PostgreSQL Crosstab Query So, assuming the respective values in the column stage are 'some_column_name_1' and 'some_column_name_2':...


2

I think you want to do something like UPDATE sciana1 SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0); COALESCE() will return you the first non-NULL parameter. Then max(id) is NULL for an empty table, which means the COALESCE expression returns 0 when the table is empty, and the actual maximum when it is not. You can further improve this by ...


2

This is the generally accepted standard for how to concatenate values contained over multiple rows. SELECT ID, LEFT(brands, LEN(brands) -2) brands FROM ( SELECT DISTINCT ID, ( SELECT brand + ', ' [text()] FROM myTable brands WHERE ...


2

You can simply group by IPDL: SELECT RAP.IDPL, MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41, MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42, MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43 FROM CHEST_UL CHT INNER JOIN UL_PUNCT_LUCRU rap ON cht.fk_id_raportor = rap.id FULL OUTER JOIN ...


2

This answer helped me solve a slightly different ambiguous column problem. I have a table where we do daily roll-ups into the same table multiple times per day. We need to re-calculate the daily roll-up on an hourly basis, which means we're updating the same row 24 times per day. Paraphrasing the above: INSERT INTO accounts as act (affiliate_code, ...


2

All this approach does in to make your original query a derived table then group by pub_id. SET hive.auto.convert.join = TRUE; SELECT max(DEAL_ID) as DEAL_ID , EXCHANGE_ID , PUB_ID , sum(INCREMENTAL) as INCREMENTAL , sum(FIRST_IMPS) as FIRST_IMPS , sum(SECOND_IMPS) as SECOND_IMPS FROM ( SELECT First.deal_id , ...


2

SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = 1 ORDER BY FIND_IN_SET(jetc.language_id, '1,2') DESC LIMIT 1


2

For obvious reasons (laziness) I used the example for creating the table and index from this post: https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/ create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 ...


1

I think you are using LEFT joins incorrectly. The way the query is now, with a column from each table involved in the WHERE clause, the joins effectively act as INNER joins, not LEFT joins. As @Kent explained in comment: When the condition is in the join statement (the ON part) the restriction is applied before the join. When it's in the WHERE, it's ...


1

SQL Server 2008. So far I've been able to fill in the enddtes by joining on ROW_NUMBER -1 and using COALESCE ;with cte as ( select row_number() over(order by startdte) as ROWNO, * from #hours) Select c1.STARTDTE, coalesce(c1.enddte, c.startdte -1) From CTE c1 LEFT JOIN CTE c On c1.ROWNO = c.ROWNO - 1


1

Solution: Reboot server I hate my life...


1

The query you show us, can never do what you show as "2nd run". You would need something like insert into ip (host_id, eth3) values ('HOST1234', inet_aton('172.16.5.83')) ON DUPLICATE KEY UPDATE eth0 = COALESCE(eth0,eth1) ,eth1 = COALESCE(eth1,eth2) ,eth2 = COALESCE(eth2,eth3) ,eth3 = COALESCE(eth3,values(eth3)) Coalesce with a null parameter ...


1

What about: SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ... FROM Table1 GROUP BY UnitID ; This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found. Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there ...


1

I've found that this solution works. It's not very elegant and I'm worried over scale(does it run the subquery twice or once) but it works. Here is the fiddle WITH subquery as ( SELECT First.deal_id ,COALESCE( First.exchange, Second.exchange ) as exchange_id ,COALESCE( First.publisher, Second.publisher ) as pub_id ,COUNT (DISTINCT(...


1

move it to up level more correct form: SELECT col1, .. colx, COALESCE(num_60_79,0) as num_60_79, ... colY FROM (second_table NATURAL FULL JOIN (SELECT assignment_id, count(*) AS num_60_79 FROM (SELECT assignment_id, mark_as_percent FROM avg_required_table ...


1

On MySQL 5.7.11 we are getting the expected results. Seems like a bug in prior versions. +---+--------+--------+------------+ | x | x | y | z | +---+--------+--------+------------+ | 1 | 2 | 1 | 1 | +---+--------+--------+------------+ | 2 | (null) | (null) | (null) | +---+--------+--------+------------+ | 3 | (null)...


Only top voted, non community-wiki answers of a minimum length are eligible