Update/Case query help

Question

Update/Case query help

Renuka__

Hall of Fame

Points: 3656
More actions
August 28, 2013 at 4:34 am

#298260

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:
ServerNameDomain
AX
BX
CY
DY
EZ
FZ
TABLE 2:
ServerNameDomain
AX
BX
CX
DY
EY
FZ
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
[font="Verdana"]Renuka__[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

danielw 23392 Newbie Points: 9 More actions · Answer 2

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

No need for anything fancy.

This should work:

update Table2

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 4

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.

[font="Verdana"]Renuka__[/font]

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

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')

Renuka__ Hall of Fame Points: 3656 More actions · Answer 6

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.

[font="Verdana"]Renuka__[/font]