Updating Multiple Columns Using Sub-Query

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 2000 Forums

SQL Server Development (2000)

Updating Multiple Columns Using Sub-Query

Author

Topic

rdugre
Starting Member

32 Posts

Posted - 2005-08-04 : 17:13:53

I read that the following UPDATE syntax is not supported in SQL Server:

UPDATE table SET (column, column, ...) = (subquery)

Is there an alternative, or do I need to write a separate query for each column being updated? Seems like there should be some sort of alternative since writing a separate query is very inefficient.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-04 : 17:16:11

If the subquery will not change, then I'd do this:

DECLARE @Var1 int

SELECT @Var1 = Column1
FROM Table1

UPDATE Table2 SET ColumnA = @Var1, ColumnB = @Var1, ColumnB = @Var1

The point is to store the value in a variable so you don't have to repeatedly run it. Then set your columns to this variable.

Tara

rdugre
Starting Member

32 Posts

Posted - 2005-08-04 : 17:30:19

Thanks for the response. Unfortunately, I am looking to update multiple columns for multiple rows in a table with summary data, so it looks like I'll need to setup another temp table to store the data before placing it into the final table via join.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-04 : 17:33:15

The example I provided would update multiple columns for multiple rows in a table. But perhaps we aren't understanding your problem. If you could show us an example (data example), then we'd be able to help you better.

Tara

rdugre
Starting Member

32 Posts

Posted - 2005-08-04 : 17:46:42

My apologies, I just figured out I have been looking at this problem entirely the wrong way. Although the issue I described is still a mystery to me, its no longer applicable to my solution.

Again, thanks for the reply.

jane.harrison
Starting Member

1 Post

Posted - 2005-08-22 : 11:51:17

Hi,

I would be interested in the answer to this problem since I am tackling the same problem...

I want to set 2 columns on a temporary table using a relatively complex select statement (including unions). As far I understand what has been written I don't see how setting a variable value will help becaue then you would set the whole column to the same value?

However in my update the data will potentially be unique for each row/column combination.

So theoretically something like this:

UPDATE procedure_report
SET value_1, account_id =
(SELECT (client.address_line1 + client.address_line2 + client.address_line3), account_id
FROM {oj client_accounts RIGHT OUTER JOIN client ON client_accounts.client_id = client.client_id RIGHT OUTER JOIN account ON client_accounts.account_id = account.account_id},
{oj company_group RIGHT OUTER JOIN account ON company_group.company_group_id = account.company_group_id}
WHERE (client_accounts.primary_contact = 'Y')
UNION ALL
SELECT (client.address_line1 + client.address_line2 + client.address_line3), account_id
FROM {oj client_accounts RIGHT OUTER JOIN client ON client_accounts.client_id = client.client_id RIGHT OUTER JOIN account ON client_accounts.account_id = account.account_id},
{oj company_group RIGHT OUTER JOIN account ON company_group.company_group_id = account.company_group_id}
WHERE (client_accounts.primary_contact Is Null))
WHERE account_id IN (SELECT account_id FROM account)

PLEASE NOTE: I'm running this on sybase not SQL Server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources