本文介绍了在表之间使用INNER JOIN更新查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

错误:Invallid对象名称p



Error: Invallid Object Name p

UPDATE p
SET Mail_Status = 'Sent'
FROM tbl_plan p
INNER JOIN tbl_Assignment a ON a.Plan_ID = p.Plan_ID
WHERE (DATEDIFF(dd, { fn NOW() }, Date) = 2) AND (Mail_Status IS NULL)

推荐答案

DECLARE @Table1 AS TABLE (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO @Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'


DECLARE @Table2 AS TABLE(Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO @Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'


SELECT *
FROM @Table1
SELECT *
FROM @Table2

UPDATE @Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM @Table1 t1
INNER JOIN @Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)


SELECT *
FROM @Table1
SELECT *
FROM @Table2


UPDATE p
SET Mail_Status = 'Sent'
FROM tbl_plan p ,tbl_Assignment a
 where a.Plan_ID = p.Plan_ID and (DATEDIFF(dd, { fn NOW() }, Date) = 2) AND (Mail_Status IS NULL)



这篇关于在表之间使用INNER JOIN更新查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-31 10:13