update test_lb1 set parent_title=(select b.title from b,test_lb1 where b.forumid=test_lb1.parentid)
update test_lb1 set parent_title=(select forum.title from forum where test_lb1.parentid=forum.forumid);
update test_lb1 set parent_title=(select forum.title from forum where test_lb1.parentid=forum.forumid)
楼上的,还是不行啊 ,运行:UPDATE test_lb1 SET parent_title=(SELECT forum.title FROM forum WHERE test_lb1.parentid = forum.forumid) 报错:You have an error in your SQL syntax near 'SELECT forum.title FROM forum WHERE test_lb1.parentid = forum.forumid ) ' at line 1
我测试成功了 UPDATE tbl_liu SET name=(SELECT area_name FROM tbl_area WHERE tbl_liu.age = tbl_area.area_cd )
哦 ,我忘说了 我用的是mysql数据库,不是mssql
不需要子查询,直接自然联接两表就可以:update test_lb1,forum set test_lb1.parent_title=froum.forumid where test_lb1.parentid = forum.forumid
DROP TABLE IF EXISTS test_lb1; CREATE TABLE test_lb1 ( id int(10) NOT NULL auto_increment, parent_title varchar(50) NOT NULL default '', parentid int(10) NOT NULL default '0', title varchar(50) NOT NULL default '', KEY id (id) ) TYPE=MyISAM AUTO_INCREMENT=247 ; CREATE TABLE `forum` ( `forumid` smallint(5) unsigned NOT NULL auto_increment, `styleid` smallint(5) unsigned NOT NULL default '0', `title` varchar(100) NOT NULL default '', `description` varchar(250) NOT NULL default '', `active` smallint(6) NOT NULL default '0', `displayorder` smallint(6) NOT NULL default '0', `doorurl` varchar(100) NOT NULL default '', `replycount` int(10) unsigned NOT NULL default '0', `lastpost` int(11) NOT NULL default '0', `lastposter` varchar(50) NOT NULL default '', `threadcount` mediumint(8) unsigned NOT NULL default '0', `allowposting` tinyint(4) NOT NULL default '0', `cancontainthreads` smallint(6) NOT NULL default '0', `daysprune` smallint(5) unsigned NOT NULL default '0', `newpostemail` varchar(250) NOT NULL default '', `newthreademail` varchar(250) NOT NULL default '', `moderatenew` smallint(6) NOT NULL default '0', `moderateattach` smallint(6) NOT NULL default '0', `allowbbcode` smallint(6) NOT NULL default '0', `allowimages` smallint(6) NOT NULL default '0', `allowhtml` smallint(6) NOT NULL default '0', `allowsmilies` smallint(6) NOT NULL default '0', `allowicons` smallint(6) NOT NULL default '0', `parentid` smallint(6) NOT NULL default '0', `parentlist` varchar(250) NOT NULL default '', `allowratings` smallint(6) NOT NULL default '0', `countposts` smallint(6) NOT NULL default '1', `styleoverride` smallint(5) NOT NULL default '0', `viewthisweek` int(10) NOT NULL default '0', `viewlastweek` int(10) NOT NULL default '0', `viewday` int(5) NOT NULL default '0', `viewlastday` int(5) NOT NULL default '0', `viewcheckdate` int(11) NOT NULL default '-1', `lastmthreadid` int(10) unsigned NOT NULL default '0', `logo` varchar(100) NOT NULL default '', `linkforumid` varchar(100) NOT NULL default '', `status` int(1) unsigned NOT NULL default '0', `threadcatcache` mediumtext NOT NULL, PRIMARY KEY (`forumid`), KEY `parentid` (`parentid`) ) TYPE=MyISAM AUTO_INCREMENT=1088 ;
FROM forum WHERE test_lb1.parentid = forum.forumid) 报错:You have an error in your SQL syntax near 'SELECT forum.title
FROM forum
WHERE test_lb1.parentid = forum.forumid ) ' at line 1
UPDATE
tbl_liu
SET
name=(SELECT
area_name
FROM
tbl_area
WHERE
tbl_liu.age = tbl_area.area_cd
)
CREATE TABLE test_lb1 (
id int(10) NOT NULL auto_increment,
parent_title varchar(50) NOT NULL default '',
parentid int(10) NOT NULL default '0',
title varchar(50) NOT NULL default '',
KEY id (id)
) TYPE=MyISAM AUTO_INCREMENT=247 ;
CREATE TABLE `forum` (
`forumid` smallint(5) unsigned NOT NULL auto_increment,
`styleid` smallint(5) unsigned NOT NULL default '0',
`title` varchar(100) NOT NULL default '',
`description` varchar(250) NOT NULL default '',
`active` smallint(6) NOT NULL default '0',
`displayorder` smallint(6) NOT NULL default '0',
`doorurl` varchar(100) NOT NULL default '',
`replycount` int(10) unsigned NOT NULL default '0',
`lastpost` int(11) NOT NULL default '0',
`lastposter` varchar(50) NOT NULL default '',
`threadcount` mediumint(8) unsigned NOT NULL default '0',
`allowposting` tinyint(4) NOT NULL default '0',
`cancontainthreads` smallint(6) NOT NULL default '0',
`daysprune` smallint(5) unsigned NOT NULL default '0',
`newpostemail` varchar(250) NOT NULL default '',
`newthreademail` varchar(250) NOT NULL default '',
`moderatenew` smallint(6) NOT NULL default '0',
`moderateattach` smallint(6) NOT NULL default '0',
`allowbbcode` smallint(6) NOT NULL default '0',
`allowimages` smallint(6) NOT NULL default '0',
`allowhtml` smallint(6) NOT NULL default '0',
`allowsmilies` smallint(6) NOT NULL default '0',
`allowicons` smallint(6) NOT NULL default '0',
`parentid` smallint(6) NOT NULL default '0',
`parentlist` varchar(250) NOT NULL default '',
`allowratings` smallint(6) NOT NULL default '0',
`countposts` smallint(6) NOT NULL default '1',
`styleoverride` smallint(5) NOT NULL default '0',
`viewthisweek` int(10) NOT NULL default '0',
`viewlastweek` int(10) NOT NULL default '0',
`viewday` int(5) NOT NULL default '0',
`viewlastday` int(5) NOT NULL default '0',
`viewcheckdate` int(11) NOT NULL default '-1',
`lastmthreadid` int(10) unsigned NOT NULL default '0',
`logo` varchar(100) NOT NULL default '',
`linkforumid` varchar(100) NOT NULL default '',
`status` int(1) unsigned NOT NULL default '0',
`threadcatcache` mediumtext NOT NULL,
PRIMARY KEY (`forumid`),
KEY `parentid` (`parentid`)
) TYPE=MyISAM AUTO_INCREMENT=1088 ;