Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Update Query

Author

Topic

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-20 : 06:32:20

I want to update a column of one table with help of select query.

following is the query.

update table1 set Code=(select a.Material from table2 as a,table1 as b where a.number=b.Code)

when i run the query following error is occured. all the table contains more than 1000 rows.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 06:47:38

use join method rather than subquery


update b 
set Code= a.Material 
from table2 as a
inner join table1 as b 
on a.number=b.Code

Also if relationship between tables is one to many you need to apply some kind of aggregation for the update otherwise results will not be expected one!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-20 : 06:58:56

Dear Visakh,

Thanks for the query, it worked perfectly.

thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 07:02:45

welcome
Do keep a note on cardinality between tables as I suggested to make sure update always works as per your expectation!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-20 : 08:12:43

Dear Visakh,

i want to take a script an entire databse in SQl 2000 server. please guide me. 2005 and 2008 i have taken.

thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-20 : 08:16:57

quote:
Originally posted by Dhanushkodi

Dear Visakh,

i want to take a script an entire databse in SQl 2000 server. please guide me. 2005 and 2008 i have taken.

thanks

Create script for all objects in a database using mssql2005
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

--
Chandu

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-20 : 08:19:08

Hi Chandu,

the given link is for 2005. i need 2000.

thanks
Dhanushkodi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:30:00

quote:
Originally posted by Dhanushkodi

Hi Chandu,

the given link is for 2005. i need 2000.

thanks
Dhanushkodi

if its for entire database why do you need to script them all out? why not backup and restore?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-21 : 06:49:33

Dear Vishak,

I have to restore the database from one server to another server(physically), so i need a script file for that database.....

Regards,
Dhanushkodi.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:52:42

quote:
Originally posted by Dhanushkodi

Dear Vishak,

I have to restore the database from one server to another server(physically), so i need a script file for that database.....

Regards,
Dhanushkodi.

hmm..still why cant you take a backup, then move the .bak file to new server and then restore from it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:54:33

see

http://www.techrepublic.com/blog/window-on-windows/how-do-i-restore-a-sql-server-database-to-a-new-server/454

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-21 : 07:06:24

Dear Visakh,

Due to logical file name issue. logical file name refer master database when i back up the database. so when i restore it , the error come as
"there is already object named in the database". so if i take the script and try to restore it.

it's a another way,i.e maybe the logical file name come with correct file name. thats why. please tell me.

thanks
Dhanushkodi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 07:12:38

You can rename it if you want

http://support.microsoft.com/kb/814576

and if you still have to go ahead with scripting use this

http://www.mssqltips.com/sqlservertip/2500/sql-server-2008-r2-generate-scripts-wizard-with-database-schema-and-data/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-21 : 07:28:29

Dear Visakh,

i tried to rename the file. after renamed the file while i am backup logical file refer master database.

the second link you mention is for 2005 and 2008. i need it for 2000.

please help me.

thanks.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 08:54:51

quote:
Originally posted by Dhanushkodi

Dear Visakh,

i tried to rename the file. after renamed the file while i am backup logical file refer master database.

the second link you mention is for 2005 and 2008. i need it for 2000.

please help me.

thanks.....

How do you think I could guess that when you posted this on 2008 forum?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources