Update from Select Statement - 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 2012 Forums

Transact-SQL (2012)

Update from Select Statement

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 14:48:24

Good afternoon, I have a select statemetn that joins a few tables and outputs various data based on the select critieria of the joins.

This works:

SELECT [CLIENTNUM],[ACCOUNT],
CASE
WHEN OFFICEA.OFFICE_NUM IS NULL THEN OFFICEB.OFFICE_NUM
ELSE
OFFICEA.OFFICE_NUM
END AS OFFICE_NUM ,
CASE
WHEN OFFICEA.OFFICE_DESC IS NULL THEN OFFICEB.OFFICE_DESC
ELSE
OFFICEA.OFFICE_DESC
END AS OFFICE_DESC,
[TOP_CLIENT],
CLIENTOFFICE,
OFFICEB.OFFICE_NUM,
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[ACCOUNTID]

FROM [SALESLOGIX].[sysdba].[ACCOUNT]

JOIN [MARKETING100].[dbo].[MARKETING100IMPORT]
ON [MARKETING100].[dbo].[MARKETING100IMPORT].[CLIENTNUM]
= [SALESLOGIX].[sysdba].[ACCOUNT].[USERFIELD10]

left JOIN [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100]
ON [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[ACCOUNTID] =
[SALESLOGIX].[sysdba].[ACCOUNT].[ACCOUNTID]

LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEA
ON OFFICEA.OFFICE_NUM
= [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[OFFICE]

LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEB
ON OFFICEB.OFFICE_DESC
= [MARKETING100].[dbo].[MARKETING100IMPORT].[CLIENTOFFICE]

-- WHERE [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[ACCOUNTID] IS NULL

GROUP BY
[CLIENTNUM],[ACCOUNT],[USERFIELD10],[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[ACCOUNTID],
[OFFICE],[TOP_CLIENT],[STATUS],OFFICEA.office_desc,
OFFICEA.OFFICE_NUM,CLIENTOFFICE,OFFICEB.OFFICE_NUM,OFFICEB.OFFICE_DESC

ORDER BY [USERFIELD10]

Now I would like to add an update to the statement but I dont really know where or how?

I would like to update the following:

UPDATE [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100]
SET
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[OFFICE] = [office_num],
[SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100].[TOP_OFFICE] = 'FY13'

from the above select statement

Sorry this was so long.

Thanks again for all of your assistance.

Bryan Holmstrom

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-10 : 15:01:01

Do you mean this?

UPDATE C
SET C.[OFFICE] = [office_num],
C.[TOP_OFFICE] = 'FY13'
FROM [SALESLOGIX].[sysdba].[ACCOUNT] A
JOIN [MARKETING100].[dbo].[MARKETING100IMPORT] B ON B.[CLIENTNUM] = A.[USERFIELD10]
LEFT JOIN [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] C ON C.[ACCOUNTID] = A.[ACCOUNTID]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEA ON OFFICEA.OFFICE_NUM = C.[OFFICE]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEB ON OFFICEB.OFFICE_DESC = B.[CLIENTOFFICE]

Cheers
MIK

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 15:09:17

Thats great MIK, but the office_num field could get a null value based on whether of not it was found in the OFFICEA table, if not it would then get populated from the join to the officeb table. Anyway to do that ?

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 15:13:34

hERE IS THE CASE STATEMENT DO DETERMINE THE UPDATE FIELDS:

CASE
WHEN OFFICEA.OFFICE_NUM IS NULL THEN OFFICEB.OFFICE_NUM
ELSE
OFFICEA.OFFICE_NUM
END AS OFFICE_NUM ,
CASE
WHEN OFFICEA.OFFICE_DESC IS NULL THEN OFFICEB.OFFICE_DESC
ELSE
OFFICEA.OFFICE_DESC
END AS OFFICE_DESC,
CASE
WHEN [TOP_CLIENT] IS NULL THEN 'FY13'
ELSE
[TOP_CLIENT] + ', FY13'
END AS TOP_CLIENT

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-07-10 : 15:26:00

My first attempt (It failed):

UPDATE C
CASE
WHEN OFFICEA.OFFICE_NUM IS NULL THEN SET C.OFFICE = OFFICEB.OFFICE_NUM
ELSE
SET C.OFFICE = OFFICEA.OFFICE_NUM
END
CASE
WHEN [TOP_OFFICE] IS NULL THEN SET C.[TOP_OFFICE] = 'FY13'
ELSE
SET C.[TOP_OFFICE] = [TOP_OFFICE] + ', FY13'
END

FROM [SALESLOGIX].[sysdba].[ACCOUNT] A
JOIN [MARKETING100].[dbo].[MARKETING100IMPORT] B ON B.[CLIENTNUM] = A.[USERFIELD10]
LEFT JOIN [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] C ON C.[ACCOUNTID] = A.[ACCOUNTID]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEA ON OFFICEA.OFFICE_NUM = C.[OFFICE]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEB ON OFFICEB.OFFICE_DESC = B.[CLIENTOFFICE]

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-11 : 01:46:57

it should be


UPDATE C
SET C.OFFICE = CASE 
                WHEN OFFICEA.OFFICE_NUM IS NULL THEN OFFICEB.OFFICE_NUM
                ELSE OFFICEA.OFFICE_NUM 
               END,
C.[TOP_OFFICE] = CASE
                   WHEN [TOP_OFFICE] IS NULL THEN 'FY13'
                   ELSE [TOP_OFFICE] + ', FY13'
                 END 
FROM [SALESLOGIX].[sysdba].[ACCOUNT] A
JOIN [MARKETING100].[dbo].[MARKETING100IMPORT] B ON B.[CLIENTNUM] = A.[USERFIELD10]
LEFT JOIN [SALESLOGIX].[sysdba].[C_ACCTS_MARKETING_100] C ON C.[ACCOUNTID] = A.[ACCOUNTID]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEA ON OFFICEA.OFFICE_NUM = C.[OFFICE]
LEFT JOIN [SALESLOGIX].[sysdba].[C_OFFICE_INFO] OFFICEB ON OFFICEB.OFFICE_DESC = B.[CLIENTOFFICE]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-11 : 03:26:40

UPDATE C
SET C.OFFICE = COALESCE(OFFICEA.OFFICE_NUM, OFFICEB.OFFICE_NUM),
C.[TOP_OFFICE] = COALESCE([TOP_OFFICE] + ', FY13', 'FY13')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources