Hot answers tagged

4

Don't repeat the target table in the FROM clause: UPDATE tab_A SET field1 = t2.field1, field2 = t2.field2, field3 = t2.field3 FROM tab_B t2 WHERE tab_A.uprn = t2.uprn; Quote from the manual Note that the target table must not appear in the from_list, unless you intend a self-join (emphasis mine)


2

This is a classic greatest-n-per-group problem. I would urge you to look at that link (and also here - search for "Within-group quotas (Top N per group)") to learn about this important SQL functionality! That second link's entire page is a great resource for learning SQL generally - plus it's specific to MySQL! In order to solve your particular problem (n = ...


2

If I understand the task correctly, then look for SELECT mt.id, mt.name FROM main_table AS mt JOIN bridge_table AS bt ON bt.main_table_id = mt.id JOIN other_table AS ot ON bt.other_table_id = ot.id GROUP BY mt.id, mt.name HAVING 2 = COUNT( DISTINCT CASE WHEN ot.name IN ('Other 1', 'Other 2') THEN ot.name END ) OR 1 = COUNT( DISTINCT CASE WHEN ot.name IN ...


1

I was finally able to replicate your issue due to you noting that LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL improved the query. DDL & DML CREATE TABLE Table_A (id int not null, constraint ClusteredIndex_A primary key (id)) INSERT INTO dbo.Table_A(id) SELECT top(50000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) ...


1

If I've understood you correctly, you want something like this (see fiddle here): SELECT p.id AS p_pid, p.product_name AS p_name, p.product_category_id AS p_cat_id, pt.product_id AS pt_pid, pt.tag_id AS pt_tag_id, -- other product_tag fields... t.tag_name AS t_name, t.slug AS t_slug_name -- other tag fields... FROM ...


1

SELECT message.Id, message.message, message.category, person.Id personId, CASE WHEN personmessage.messageId IS NULL THEN 0 ELSE 1 END enabled FROM message CROSS JOIN person LEFT JOIN personmessage ON personmessage.personId = person.Id AND personmessage.messageId = ...


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