SQLTeam.com | Weblogs | Forums

Combine two tables into third


#1

How can I update a table from two tables. The two tables have the same colum names, but the third is different?


#2

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


#3

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


#4

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

#5

Thank you the query now runs but it returns (0 row(s) affected). The table VyfjaarontledingsPerTipe is thus not updated.

Regards


#6

By the way there is a match for plaasno in both tables.


#7

Can you post some sample data and table definitions


#8

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.


#9

OK. I figured out how to paste sample data.Ontledings_Vyfjaar_Histories.csv (836.8 KB)
Vragte_Vyfjaar_Histories.csv (2.9 MB)


#10

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

#11

Thank you scarela, but it also runs returning (0 row(s) affected).

Regards