假设 contact 有ID为主键,并且contact 和employee 的主键均不为identity可以用下面的语句select identity(int,1,1) idindex,[ID] into #temp1 from contact Select identity(int,1,1) idindex,[ID] into #temp2 from employee where job_title = 'Drapery CSR 1'update contact set scr = t2.id from #temp1 t1 inner join #temp2 t2 on t1.idindex = t2.idindex and t1.[id] = contact.[id]
按照楼上的方法 select identity(int,1,1) idindex,CSR into #temp1 from contact Select identity(int,1,1) idindex,employee_id into #temp2 from employee where job_title = 'Drapery CSR 1'update contact set CSR = t2.employee_id from #temp1 t1 inner join #temp2 t2 on t1.idindex = t2.idindex and t1.CSR = contact.CSR出现错误 The column prefix 'contact' does not match with a table name or alias name used in the query.
句话的返回结果多于一个,不能用于Set赋值语句.因为Set语句要求的是一个唯一的确定的值.
set CSR = (Select top 1 ID from employee where job_title = 'Drapery CSR 1')
每次CSR能得到SELECT的一个值 SQL能实现这个吗?
假设 contact 有ID为主键,并且contact 和employee 的主键均不为identity可以用下面的语句select identity(int,1,1) idindex,[ID] into #temp1 from contact
Select identity(int,1,1) idindex,[ID] into #temp2 from employee where job_title = 'Drapery CSR 1'update contact set
scr = t2.id
from #temp1 t1 inner join #temp2 t2
on t1.idindex = t2.idindex and t1.[id] = contact.[id]
select identity(int,1,1) idindex,CSR into #temp1 from contact
Select identity(int,1,1) idindex,employee_id into #temp2 from employee where job_title = 'Drapery CSR 1'update contact set
CSR = t2.employee_id
from #temp1 t1 inner join #temp2 t2
on t1.idindex = t2.idindex and t1.CSR = contact.CSR出现错误
The column prefix 'contact' does not match with a table name or alias name used in the query.
意思是,你是不是在指定的数据库下面?