在表之间使用INNER JOIN更新查询？ [英] Update Query with INNER JOIN between tables?

问题描述

错误：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)

检查这个...

http://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update- statement-multiple-tables-in-update-statement / [ ^ ]

Check this...
http://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement/[^]

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