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)
answered Dec 27 '19 at 11:41
a_horse_with_no_name
50.3k10 gold badges98 silver badges131 bronze badges
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
