Access迁升到SQL出错。求救！！！急！！谢谢！！

[xiefeng]

迁升后报错：
Microsoft VBScript 编译器错误 错误 '800a03f6'

缺少 'End'

/iisHelp/common/500-100.asp，行242

Microsoft OLE DB Provider for SQL Server 错误 '80040e14'

在关键字 'left' 附近有语法错误。

/manger/admin_Column.asp，行320


下面是原语句：
update [NB_Content] left join [NB_Column] c on [NB_Content].columnid=c.id set columncode=c.code,columnname=c.title where columncode like '"&SourCode&"%'

[xiefeng]

在线等待,谢谢各位大虾米！！

[baikaishui_0825]

"......like '%"&SourCode&"%'"

[fantiny]

sqlserver 允许left join后更新数据吗？

[xiefeng]

fantiny(喻捷)：问题就是出在这里，我测试了要多次，可能是SQL不支持left join进行数据更新。
那么在SQL中要用什么语句来代替呢？？我对SQL不是很熟。我查了许多相关的资料没有找到这种更新的SQL方法还请大家多多帮忙！谢谢！！！

[tianch]

应该这样吧,update中能用left join？
update [NB_Content] a set columncode=c.code,columnname=c.title from (select code,title from [NB_Column] where id=a.columnid) c where a.columncode like '" & SourCode & "'%"

[baikaishui_0825]

Transact-SQL 参考


UPDATE
更改表中的现有数据。

语法
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]

{ { [ FROM { < table_source > } [ ,...n ] ]

[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >

< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table >

< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ < join_hint > ]
JOIN

< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| {LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}

[baikaishui_0825]

try it:
update [NB_Content]
set columncode=c.code,columnname=c.title
from [NB_Content] left join [NB_Column] c on [NB_Content].columnid=c.id
where columncode like '%"&SourCode&"%'

[xiefeng]

谢谢！谢谢各位大虾米！！！结贴！