How can I update a table from two tables. The two tables have the same colum names, but the third is different?
You can use the update clause in combination with a select clause and joins.
Example:
UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_B
INNER JOIN Other_Table AS Table_C
ON Table_B.id = Table_C.id
Thank you. I have replaced the Table_A,Table_B and Table_C with my table names.
UPDATE
VyfjaarontledingsPerTipe
SET
VyfjaarontledingsPerTipe.SuikerVrag = Ontledings_Vyfjaar_Histories.suiker,
VyfjaarontledingsPerTipe.OntledingsdatumMonster = Ontledings_Vyfjaar_Histories.Datum
FROM
Some_Table AS Ontledings_Vyfjaar_Histories
INNER JOIN Other_Table AS Vragte_Vyfjaar_Histories
ON Ontledings_Vyfjaar_Histories.Plaasno = Vragte_Vyfjaar_Histories.Plaasno
I am getting many errors on that. The first problem (lets start there) is with Ontledings_Vyfjaar_Histories.suiker (the multipart identifier Ontledings_Vyfjaar_Histories.suiker could not be bound). I am using intellisense so it could not be a typo. The second problem is with Some_Table (Msg 208, Level 16, State 1, Line 1
Invalid object name 'Some_Table'.) What am I missing?
Regards
You have to change the name of your tables. You just changed the alias of the tables.
Here is the error
Some_Table AS Ontledings_Vyfjaar_Histories
It should be
Ontledings_Vyfjaar_Histories
UPDATE
VyfjaarontledingsPerTipe
SET
VyfjaarontledingsPerTipe.SuikerVrag = Ontledings_Vyfjaar_Histories.suiker,
VyfjaarontledingsPerTipe.OntledingsdatumMonster = Ontledings_Vyfjaar_Histories.Datum
FROM
Ontledings_Vyfjaar_Histories
INNER JOIN Vragte_Vyfjaar_Histories
ON Ontledings_Vyfjaar_Histories.Plaasno = Vragte_Vyfjaar_Histories.Plaasno
Thank you the query now runs but it returns (0 row(s) affected). The table VyfjaarontledingsPerTipe is thus not updated.
Regards
By the way there is a match for plaasno in both tables.
Can you post some sample data and table definitions
Thank you for your patience and help.
[dbo].[Ontledings_Vyfjaar_Histories](
[Plaasno] varchar NULL,
[Blokno] varchar NULL,
[Datum] [date] NULL,
[suiker] [real] NULL,
[suur] [real] NULL,
[ph] [real] NULL,
[Tipe] char NULL,
[Alias] varchar NULL,
[Aliasnaam] varchar NULL,
[Plaasnaam] varchar NULL
[dbo].[Vragte_Vyfjaar_Histories](
[Plaasno] varchar NULL,
[Blokno] varchar NULL,
[Datum] [date] NULL,
[suiker_min5] [real] NULL,
[suiker_min4] [real] NULL,
[suiker_min3] [real] NULL,
[suiker_min2] [real] NULL,
[suiker_min1] [real] NULL,
[Suiker] [real] NULL,
[Tipe] char NULL,
[Alias] varchar NULL,
[Aliasnaam] varchar NULL,
[Plaasnaam] varchar NULL
[dbo].[VyfjaarontledingsPerTipe](
[Aliasnaam] varchar NULL,
[Plaasnaam] varchar NULL,
[Plaasno] varchar NULL,
[BlokNo] varchar NULL,
[Kult] varchar NULL,
[OntledingsdatumMonster] [date] NULL,
[SuikerMonster] [real] NULL,
[OntledingsdatumVrag] [date] NULL,
[SuikerVrag] [real] NULL,
[PlaasBlok] varchar NULL,
[Ontledingstipe] char NULL
Not sure how to paste sample data.
OK. I figured out how to paste sample data.Ontledings_Vyfjaar_Histories.csv (836.8 KB)
Vragte_Vyfjaar_Histories.csv (2.9 MB)
the column Plassno is common in all the tables. Try this
UPDATE
VyfjaarontledingsPerTipe
SET
VyfjaarontledingsPerTipe.SuikerVrag = Ontledings_Vyfjaar_Histories.suiker,
VyfjaarontledingsPerTipe.OntledingsdatumMonster = Ontledings_Vyfjaar_Histories.Datum
FROM
VyfjaarontledingsPerTipe
INNER JOIN
Ontledings_Vyfjaar_Histories
ON VyfjaarontledingsPerTipe.Plaasno= Ontledings_Vyfjaar_Histories.Plaasno
INNER JOIN Vragte_Vyfjaar_Histories
ON Ontledings_Vyfjaar_Histories.Plaasno = Vragte_Vyfjaar_Histories.Plaasno
Thank you scarela, but it also runs returning (0 row(s) affected).
Regards