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 2012 Forums
 Transact-SQL (2012)
 Update from Select Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 07/10/2013 :  14:48:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/10/2013 :  15:01:01  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 07/10/2013 :  15:09:17  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 07/10/2013 :  15:13:34  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 07/10/2013 :  15:26:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/11/2013 :  01:46:57  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30216 Posts

Posted - 07/11/2013 :  03:26:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
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.08 seconds. Powered By: Snitz Forums 2000