How Can i Update many fields by using SQL

I got myInvTable, myCompanyTable,
I need to update
CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Compan
yContact.
How can I write it by one SQL statment '
Thanks a lotYou can update only one table at a time in a single Transact-SQL statement.
It is unclear from your post what columns belong to which tables. Single
table UPDATE example:
UPDATE myCompanyTable
SET
CompanyAdd1 = 'address 1',
CompanyAdd2 = 'address 2',
CompanyAdd3 = 'address 3',
CompanyAdd4 = 'address 14',
CompanyTel = 'tel 1',
CompanyFax = 'fax 1',
CompanyContact' = 'contact 1'
WHERE myCompanyTableID = 1
Hope this helps.
Dan Guzman
SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23U2uIF%23EFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I got myInvTable, myCompanyTable,
> I need to update
> CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Comp
anyContact.
> How can I write it by one SQL statment '
> Thanks a lot
>|||OH, I need to update myInvtable indeed. update the information from the
companytable
Can I
update myinvTable
set companyadd1 = myCompanyTable.companyadd1,
companyadd2 = myCompanyTable.companyadd2,
....etc
where .. myInvTable.companycode = myCompanyTable.companycode ?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:OZroCL%23EFHA.1936@.TK2MS
FTNGP14.phx.gbl...
> You can update only one table at a time in a single Transact-SQL
> statement. It is unclear from your post what columns belong to which
> tables. Single table UPDATE example:
> UPDATE myCompanyTable
> SET
> CompanyAdd1 = 'address 1',
> CompanyAdd2 = 'address 2',
> CompanyAdd3 = 'address 3',
> CompanyAdd4 = 'address 14',
> CompanyTel = 'tel 1',
> CompanyFax = 'fax 1',
> CompanyContact' = 'contact 1'
> WHERE myCompanyTableID = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:%23U2uIF%23EFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||You can use subqueries to solve this problem
update myinvTable
set companyadd1 = (select companyadd1 from myCompanyTable where
companycode=.... ),
companyadd2 = (select companyadd2 from myCompanyTable where companycode=
.... ),,....etc
where companycode = ....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OaPdiP%23EFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OH, I need to update myInvtable indeed. update the information from the
> companytable
> Can I
> update myinvTable
> set companyadd1 = myCompanyTable.companyadd1,
> companyadd2 = myCompanyTable.companyadd2,
> ....etc
> where .. myInvTable.companycode = myCompanyTable.companycode ?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
glsD:OZroCL%23EFHA.1936@.TK2MSFTNGP14.phx.gbl...
CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Compan
yContact.
>|||On Wed, 16 Feb 2005 12:54:27 +0800, Agnes wrote:

>OH, I need to update myInvtable indeed. update the information from the
>companytable
>Can I
(snip)
Hi Agnes,
You can, but you shouldn't. You'd be storing redundant data in your
database.
It's far better to join the Invoices table to the Companies table in the
code that prints invoices.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names> ) REFERENCES <other table name> (<column names> ) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How can i uninstall MDAC

i need to uninstall MDAC from my system. my operating system is windows2000 proffessional.
pls help me... its very urgent....Don't know which version of MDAC you're referring, but you can uninstall MDAC 9.0 by launching mdacrb.exe.

If you want to remove the MDAC 2.6/2.7 or so, you can use the Component Checker (available from msdn.microsoft.com) to remove it from the system, or use dasetup.exe /U which rolls back to the previous
version of MDAC.

How can I 'unfold' a count to 1, 2, 3,..., n records?

Hi all,
I have a simple query that retrieves the number of articles that an order
has:
SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrder
Both ArticleId and ArticleCount are integers. A sample set result woud be
like this one:
ArticleId ArticleCount
1 3
2 2
3 5
4 1
I need to print the labels for each single article that is sold. I need to
expand/unfold that result set into something like:
ArticleId ArticleCountId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
will need 3 labels (named 1, 2 and 3), and so on.
I've been thinking about how to acomplish this but the only thing that goes
to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
However, since passing a table to a SP is not possible I'm a little
stuck-in-the-mud :(
PD: I'm using Reporting Services so it is a little hard to do job with a
simple FOR statement since RS is not very good for being programmed the way
we were used with VB. Of course I could do an assembly and do the
programming there but I would rather do the job in the query/sql server and
let RS as neat/simple as possible.
Any ideas? Regards and thanks in advance.Hi
If you have a number table or derived table of numbers, then something like
the following would work:
SELECT A.ArticleId, C.Num
FROM ( SELECT 1 As Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5 ) C
JOIN (SELECT 1 AS ArticleId, 3 AS ArticleCount
UNION ALL SELECT 2, 2
UNION ALL SELECT 3, 5
UNION ALL SELECT 4, 1 ) A ON C.Num <= A.ArticleCount
ORDER BY A.ArticleId, C.Num
John
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||David
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Test
(
col1 INT,
col2 INT
)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,2)
SELECT col1,n FROM #Test LEFT JOIN Numbers
ON #Test.col2 >= Numbers.n
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
@.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
goes
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
way
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
and
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||You can create a digits table and cross join it.
e.g.
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
SELECT ArticleId, ArticleCount=digit
FROM TOrderDetails cross join digits
WHERE OrderId = @.MyOrder
and ArticleCount>=digit
ORDER BY ArticleId
-oj
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||Instead of counting try ranking:
DROP TABLE #article_sales
CREATE TABLE #article_sales ( as_id INT IDENTITY, article_id TINYINT,
sale_date DATETIME )
INSERT INTO #article_sales SELECT 1, '1 Apr 2005'
INSERT INTO #article_sales SELECT 1, '2 Apr 2005'
INSERT INTO #article_sales SELECT 1, '3 Apr 2005'
INSERT INTO #article_sales SELECT 2, '1 Apr 2005'
INSERT INTO #article_sales SELECT 2, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '1 Apr 2005'
INSERT INTO #article_sales SELECT 3, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '3 Apr 2005'
INSERT INTO #article_sales SELECT 3, '4 Apr 2005'
INSERT INTO #article_sales SELECT 3, '5 Apr 2005'
INSERT INTO #article_sales SELECT 4, '1 Apr 2005'
-- Your count query
SELECT article_id, COUNT(*)
FROM #article_sales
GROUP BY article_id
-- Instead of counting, try ranking
SELECT
as1.article_id,
( SELECT COUNT(*) FROM #article_sales as2 WHERE as1.article_id =
as2.article_id AND as2.as_id < as1.as_id ) + 1 rank
FROM #article_sales as1
Let me know how you get on.
Damien
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||Thansk Uri & Oj for your hint about using an auxiliary table with numbers
and a cross join. It really did the job much easier than I thought it would
be.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribi en el mensaje
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>

How can I turn off the encrypted login feature?

Hi all.

My question is, is there any option that turning of the login encrypt?

I've read many post that they said the login encryption is always on and can't turn that off.
With MSSQL2K, I made some program that audit who try to login and when and access which db.
But I can't check with MSSQL2K5 because the server and client always encrypt the login info (via TLS?)

Someone said that profile the client but when the site is busy, that feature give heavy stress to server..
The port mirror server is another machine so I need 1. decrypt the login info or 2. turn off the login encryption feature.
Is there any possible way to solve this?

Thanks for reading this.

If your goal is to audit SQL Server login's and access to SQL Server objects, you can consider SQL Server auditing. E.g., the following link in SQL Server 2005 books online describes how to turn on Login Auditing through SQL Server Management Studio: http://msdn2.microsoft.com/en-US/library/ms175850.aspx.

|||

Yes, my goal is audit the login information and other info also.

But, audit in the local server do the heavy stress to itself and also need more and more hard disk space. Especially the site QPS is more than 10000..

So, there is no way to turn off the login encryption?

Anyway, thanks for your answer, it's very helpful information.

|||

Hi Hopi,

No, there is no way to turn of login encryption for SQL Server 2005. The server will always encrypt the login packet whenever possible. And with the server's new capability of generating a self-signed cert, this most of the time.

Il-Sung.

|||

Hi. Il-Sung.

Thank you for your answer. And here's final question.

1. Is the self-signed cert generated whenever the SQL service stared? Or that cert generated only once that installed into server?

2. Where can I found that cert?

3. Is there any document for this?

Hopi

|||

Hi Hopi,

I apologize for the delay. Here are the answers to your questions:

1 - The self-signed cert is generated each time the service is started

2 - The cert is stored internally and is not accessible or queryable by any external interface

3 - The BOL topic Encrypting Connections to SQL Server mentions this:

"By default, credentials transmitted when a client application connects to SQL Server 2005 are encrypted. If a certificate signed by a mutually-trusted certification authority is not available, the self-signed certificate will be used."

Thanks,
Il-Sung

|||

Thank for your kind answer.

I still didn't resolve this but maybe a week or so.. I think I can fix our solution. Not by turning off the encryption.
Thanks again!

B/R
Hopi