2

I am a complete beginner to SQL Server, and I have reached my limit.

Currently I am using a script to update a table from another table using a column. Since both databases are assigned to 2 different 3rd party software, I created a .bat script to use for task manager in windows server, that way it can update every 10 minutes.

While this is tested and works, I feel there has to be a way to create a relationship between the two databases without having to use the task. 

UPDATE therefore.dbo.thecat51
SET num_factura = 
 (SELECT therefore.dbo.documentos.num_factura
 FROM therefore.dbo.Documentos
 WHERE therefore.dbo.thecat51.num_albaran=therefore.dbo.documentos.num_albaran)
WHERE therefore.dbo.thecat51.num_albaran = 
 ( SELECT therefore.dbo.documentos.num_albaran
 FROM therefore.dbo.Documentos
 WHERE therefore.dbo.thecat51.num_Albaran = therefore.dbo.documentos.num_albaran)

Also, we are using SQL Server Express, so I don't have the option to create a scheduled job.

giannis christofakis
  • 8,201
  • 4
  • 54
  • 65
Alex E.
  • 139
  • 3
  • 14
  • linked server is probably what you are looking at -> https://www.google.fr/search?q=linked+server+sql+server&ie=utf-8&oe=utf-8&client=firefox-b-ab&gfe_rd=cr&ei=J0pIV6TBE-2v8weI1oLoAg – Andy K May 27 '16 at 13:23

2 Answers2

0

You can do the UPDATE with an INNER JOIN to perform the update you need:

UPDATE A SET
    num_factura = B.num_factura
FROM therefore.dbo.thecat51 A
    INNER JOIN therefore.dbo.Documentos B
        ON A.num_albaran = B.num_albaran
Jason W
  • 13,026
  • 3
  • 31
  • 62
0

Use an INNER JOIN between your two tables. At the time I posted this, you had not told us which RDBMS you are using so I will give answers for SQL Server and MySQL:

SQL Server:

UPDATE t1 
SET t1.num_factura = t2.num_factura
FROM therefore.dbo.thecat51 AS t1
INNER JOIN therefore.dbo.Documentos AS t2
    ON t1.num_albaran = t2.num_albaran

MySQL:

UPDATE therefore.dbo.thecat51 AS t1
INNER JOIN therefore.dbo.Documentos AS t2
    ON t1.num_albaran = t2.num_albaran
SET t1.num_factura = t2.num_factura
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I tried and it works , but it works the same way , meaning , i need to manually update the table , or create the script that executes every 10 minutes . What i was asking is a way to link the two tables so that they update automatically everytime a value is added. Thanks for the fast response. – Alex E. May 27 '16 at 14:07
  • Sorry for the late response, but i've been doing other work. I searched about triggers and that seems to be what i'm looking for. – Alex E. Jun 01 '16 at 15:13