开发者

SQL update only if relationship exists

开发者 https://www.devze.com 2023-03-13 07:46 出处:网络
I have batches and tasks. Upon batching some tasks into another batch I want to close all batches that are now empty by marking deleted = true. I tried to simply select using a join only the batches t

I have batches and tasks. Upon batching some tasks into another batch I want to close all batches that are now empty by marking deleted = true. I tried to simply select using a join only the batches that have no tasks.:

SE开发者_运维知识库LECT id FROM batches INNER JOIN tasks on batches.id = tasks.batch_id where count(tasks.id) > 0 

But this does not seem to work.


Change to an outer join. Inner join won't work to get non-matches.

SELECT id FROM batches LEFT JOIN tasks on batches.id = tasks.id where tasks.id is null


If I am not misunderstanding your problem, this is what you are looking for.

UPDATE tbl
SET col=tbl.col
FROM tbl 
INNER JOIN tbl1 ON tbl1.SomeCol=tbl.SomeCol
WHERE ......
0

精彩评论

暂无评论...
验证码 换一张
取 消