sql语句中关于UPDATE的语法问题

piaopiaoahuge 2012-05-16 07:30:14

请各位大侠给看下这句UPDATE的问题出在那边啊，因为是初学数据库，所以好多不懂，请多多指教
UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET (The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Bus'],The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Labe'])=(SELECT The_Match_Data_OldBusToNewBus_guize['New BusName'],The_Match_Data_OldBusToNewBus_guize['NEW Label'] FROM The_Match_Data_OldBusToNewBus_guize
上面这句话提示是语法错误，但是不知道错在那边，网上查了好几种办法也不好用啊……

...全文

236 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

随风飘扬的叶 2012-05-17

打赏
举报

建议楼主去看一下SQL语句应用的资料。

piaopiaoahuge 2012-05-17

打赏
举报

[Quote=引用 10 楼的回复:]

T-SQL update的语法是这样的

update tableName set 字段一=新的字段值,字段二=新的字段值,字段三=新的字段值..... where 字段N=新的字段值
[/Quote]
谢谢关注：请帮我解决下这个问题：
UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus]=(SELECT [New BusName] FROM The_Match_Data_OldBusToNewBus_guize WHERE [Old BusName]='" + temp + "' AND [Old Label]='" + temp2 + "') WHERE The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus]='" + temp + "' AND The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Label]='" + temp2 + "'
但是会提示我：操作必须使用一个可更新的查询

piaopiaoahuge 2012-05-17

打赏
举报

[Quote=引用 9 楼的回复:]

看看你查的那个表里需要更新的数据存在么？
[/Quote]
你好！刚才我查过了，用这个语句
SELECT The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus] FROM The_Match_Data_OldBusToNewBus_by_Labels_shiyan WHERE The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus]='A-IOC-11L' AND The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Label]='150'
查出需要更新的数据字段在这个条件下是有值的，只是不唯一，有好几个，这样可以更新吗，请指教，谢谢……

webwolfa 2012-05-17

打赏
举报

T-SQL update的语法是这样的

update tableName set 字段一=新的字段值,字段二=新的字段值,字段三=新的字段值..... where 字段N=新的字段值

cat545 2012-05-17

打赏
举报

看看你查的那个表里需要更新的数据存在么？

piaopiaoahuge 2012-05-17

打赏
举报

[Quote=引用 7 楼的回复:]

我估计是SQLServer数据库，Oracle数据库都不允许这么长的命名，好像
[/Quote]
你好！我用的是Access数据库，出现的错误是：操作必须使用一个可更新的查询。请指教……

仙剑 2012-05-17

打赏
举报

我估计是SQLServer数据库，Oracle数据库都不允许这么长的命名，好像

piaopiaoahuge 2012-05-17

打赏
举报

[Quote=引用 5 楼的回复:]

SQL code

UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Bus']=(SELECT The_Match_Data_OldBusToNewBus_guize['New BusName'],The_Match_Data_Ol……
[/Quote]
按照你的意思我把语句改成了下面这句话：
UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus]=(SELECT [New BusName] FROM The_Match_Data_OldBusToNewBus_guize WHERE [Old BusName]='" + temp + "' AND [Old Label]='" + temp2 + "') WHERE The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Bus]='" + temp + "' AND The_Match_Data_OldBusToNewBus_by_Labels_shiyan.[Label]='" + temp2 + "'
但是会提示我：操作必须使用一个可更新的查询。请你给看下，不太清楚是怎么回事啊……谢谢

piaopiaoahuge 2012-05-17

打赏
举报

谢谢各位大侠的关注和指教，问题已经解决了……
问题的关键在于Access不支持嵌套语句，我把UPDATE语句拆分了问题就好了，还是高手的一句话值钱啊
下面我把我调试通过的源码贴出来供大家学习，也希望高手能够再给我提意见改进……
private void modifyDataBasesDataToolStripMenuItem_Click(object sender, EventArgs e)
{
//连接数据库
string Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ARJ21.mdb";
OleDbConnection myconn = new OleDbConnection(Strcon);
myconn.Open();
OleDbCommand comm = new OleDbCommand("SELECT [Old BusName],[New BusName],[Old Label],[NEW Label] FROM T_guize", myconn);
OleDbDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string temp = dr.GetValue(0).ToString();
string temp1 = dr.GetValue(1).ToString();
string temp2 = dr.GetValue(2).ToString();
string temp3= dr.GetValue(3).ToString();
if ((temp == temp1) && (temp2 == temp3))
{
continue;
}
else
{
OleDbCommand comm2 = new OleDbCommand("UPDATE T_shiyan SET T_shiyan.[Bus]='" + temp1 + "',T_shiyan.[Label]='" + temp3 + "' WHERE T_shiyan.[Bus]='" + temp + "' AND T_shiyan.[Label]='" + temp2 + "' ", myconn);
comm2.ExecuteNonQuery();
comm2.Dispose();
}
}
dr.Close();
myconn.Close();
}

EnForGrass 2012-05-16

打赏
举报



UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Bus']=(SELECT The_Match_Data_OldBusToNewBus_guize['New BusName'],The_Match_Data_OldBusToNewBus_guize['NEW Label'] FROM The_Match_Data_OldBusToNewBus_guize),The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Labe']=(SELECT The_Match_Data_OldBusToNewBus_guize['NEW Label'] FROM The_Match_Data_OldBusToNewBus_guize)

确保(SELECT The_Match_Data_OldBusToNewBus_guize['NEW Label'] FROM The_Match_Data_OldBusToNewBus_guize)查出来的值是唯一的。还有字段名The_Match_Data_OldBusToNewBus_guize['New BusName']这个还有特殊字符，可能会出问题

piaopiaoahuge 2012-05-16

打赏
举报

不好意思，我把整个语句贴出来吧，谢谢各位大侠关注
UPDATE The_Match_Data_OldBusToNewBus_by_Labels_shiyan SET (The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Bus'],The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Labe'])=(SELECT The_Match_Data_OldBusToNewBus_guize['New BusName'],The_Match_Data_OldBusToNewBus_guize['NEW Label'] FROM The_Match_Data_OldBusToNewBus_guize) WHERE The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Bus']='" + temp + "' AND The_Match_Data_OldBusToNewBus_by_Labels_shiyan['Label']='" + temp2 + "'这个是我的原来的语句

piaopiaoahuge 2012-05-16