Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update/Case query help Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 4:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Hi,

I am trying to write an update query but not able to get the result as I want. Any help is much appreciated.

I have 2 table as shown below

TABLE 1:

ServerName Domain
A X
B X
C Y
D Y
E Z
F Z

TABLE 2:

ServerName Domain
A X
B X
C X
D Y
E Y
F Z

The domains are different in table 2 for the same servernames. All I am trying to do is to update the domain column in Table 2 same as Table 1 (as domain column in Table 1 is accurate) for all the matching servers and for the servers not matching I want to update it as Unknown.

Thank you
Renuka


Renuka__
Post #1489109
Posted Wednesday, August 28, 2013 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Write a SELECT statement joining the two tables which outputs the PK of the table you want to update, the existing domain value, and the value you want to change it to.
Check that the output matches your requirements.
It's then a simple matter to convert this SELECT into an UPDATE...FROM. If you are unsure then post the SELECT and someone will step in to help you.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1489130
Posted Wednesday, August 28, 2013 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:27 AM
Points: 3, Visits: 110
Hi,

Could you not do this using a MERGE statement?

So using table 'Table 1' as the SOURCE and 'Table 2' as the TARGET?

When MATCHED - Update the Domain column in Table 2 to be Table1.Domain

When NOT MATCHED - Update the Domain column in Table 2 to be 'Unknown'

Dan
Post #1489131
Posted Wednesday, August 28, 2013 5:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
No need for anything fancy.

This should work:

update Table2
set Domain = isnull((select Domain from Table1 t1 where t1.ServerName = Table2.ServerName), 'Unknown')

Post #1489153
Posted Friday, August 30, 2013 5:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Thank you for the replies.

I was trying to avoid complex queries too; however, inner Select query in the UPDATE statement returns multiple domain names, so it returns 'subquery returned more than 1 value' error and the update fails.

I am still trying...

Thank you.


Renuka__
Post #1490082
Posted Friday, August 30, 2013 5:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
Renuka__ (8/30/2013)
Thank you for the replies.

I was trying to avoid complex queries too; however, inner Select query in the UPDATE statement returns multiple domain names, so it returns 'subquery returned more than 1 value' error and the update fails.

I am still trying...

Thank you.


If you are using my code this error means that you have multiple domains in table1 for at least one server.
This means that you must decide which Domain you wan to use in this case.

If using the last domain in alphabetic order is ok for you something like this should work:

update Table2
set Domain = isnull((select max(Domain) from Table1 t1 where t1.ServerName = Table2.ServerName), 'Unknown')

Post #1490099
Posted Friday, August 30, 2013 9:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Thank you Stefan_G.

Yes; we do have some servers with same name in one domain and non-domain(workgroup servers). Max(domain) does work, but unfortunately that is not the case with all the servers. This query was needed for a report and for now we have fixed it by adding an additional column to specify if one is a valid domain or not and report it based on that.

Before adding the additional columns, the issue was infact fixed using 'MERGE'.

Thank you all for the replies and suggestions.


Renuka__
Post #1490204
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse