CODEWITHSUNDEEP
sqljoininsert
horseshoe
horseshoe

Reputation: 1477

SQL Insert values by join

Perhaps it's too late and that's why I am stuck. I want to fill a database with values but only for those values where a date already exists in the data base. The structure is the following:

CREATE TABLE dbo.TEST(ID INT IDENTITY(1,1) NOT NULL,date_time datetime)

ALTER TABLE dbo.TEST ADD "column1" FLOAT;

INSERT INTO dbo.TEST(date_time,"column1")
  VALUES ('2018-04-01 02:00:00',1),('2018-04-01 06:00:00',2),('2018-04-01 07:00:00',3) 

ALTER TABLE dbo.TEST ADD "column2" FLOAT;

so the table looks like this:

enter image description here

now I want to fill column2 for 2:00 and 6:00 o clock with the values 20 and 21 but ignore value 25. What I have is 

INSERT INTO dbo.TEST("column2")
        SELECT
            NewValues."column2"
        FROM (
          VALUES  ('2018-04-01 02:00:00',20),
                  ('2018-04-01 06:00:00',21),
                  ('2019-10-05 20:30:00',25)
              ) AS NewValues(date_time,"column2")
        INNER JOIN dbo.TEST
        ON dbo.TEST.date_time = NewValues.date_time
   WHERE dbo.TEST.date_time = NewValues.date_time

but this results in

enter image description here

what am I missing?

Upvotes: 2

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want an update:

update t
    set column2 = NewValues.column2
    from dbo.TEST t left join
         (values  ('2018-04-01 02:00:00', 20),
                  ('2018-04-01 06:00:00', 21),
                  ('2019-10-05 20:30:00', 25)
              )  NewValues(date_time, column2)
        ON t.date_time = NewValues.date_time;

Upvotes: 2

Related Questions