SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Update Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/20/2013 :  06:32:20  Show Profile  Reply with Quote

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

India
52317 Posts

Posted - 06/20/2013 :  06:47:38  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/20/2013 :  06:58:56  Show Profile  Reply with Quote
Dear Visakh,

Thanks for the query, it worked perfectly.

thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/20/2013 :  07:02:45  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/20/2013 :  08:12:43  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/20/2013 :  08:16:57  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/20/2013 :  08:19:08  Show Profile  Reply with Quote
Hi Chandu,

the given link is for 2005. i need 2000.

thanks
Dhanushkodi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:30:00  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/21/2013 :  06:49:33  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:52:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:54:33  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/21/2013 :  07:06:24  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  07:12:38  Show Profile  Reply with Quote
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
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/21/2013 :  07:28:29  Show Profile  Reply with Quote
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.....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  08:54:51  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000