All geek questions in one place

Select one database to update another

I need to update a table in sql server from one database to another, and I was wondering how to do it with

here is my example

UPDATE [Logix].[sysdba].[TICKET] ( STATUSCODE) SELECT TICKETID, GM_NUMBER, STATUSCODE FROM [Logix_Dev].[sysdba].[TICKET] where GM_NUMBER <> ''

but I feel that this is the wrong syntax ... basically, what I want to do is select all the entries from the TICKET table in the Logix_Dev database and update the status code in the Logix database where TICKETID corresponds ....

+4
source share
2 answers

Use aliases to make your life easier (in the same instance):

 UPDATE l SET l.STATUSCODE=s.someVal FROM Logix.sysdba.Ticket l INNER JOIN Logix_dev.sysdba.ticket s ON s.ID = l.ID

Of course, you will need to replace s.ID and l.ID with the corresponding columns (TicketID?):

 UPDATE l SET l.STATUSCODE=s.STATUSCODE FROM Logix.sysdba.Ticket l INNER JOIN Logix_dev.sysdba.ticket s ON s.TicketID = l.TicketID WHERE s.GM_NUMBER = ''

If they are in one instance, then two points (..) are not needed. If it is a linked server or another instance, you need two points (..):

Linked server:

 UPDATE l SET l.STATUSCODE=s.someVal FROM Logix.sysdba.Ticket l INNER JOIN Logix_dev..sysdba.ticket s ON s.ID = l.ID
+6
source

Here he is:

 UPDATE [Logix].[sysdba].[TICKET] ( STATUSCODE) SELECT TICKETID, GM_NUMBER, STATUSCODE FROM [Logix_Dev]..[sysdba].[TICKET] where GM_NUMBER <> ''

Note the double dot after the database name.

0
source

Source: https://habr.com/ru/post/1380051/

More articles:


All Articles