CODEWITHSUNDEEP
sql
David Goldstone
David Goldstone

Reputation: 23

Updating a column in SQL table with a condition

enter image description here

Please can someone help me with the SQL syntax?. See attached dataset. I am trying to update the NULLS in 2019 for the Sales Type column with the same value as in 2020 for the relevant Customername. EG. Sales Type in 2019 needs to say Social for Customername ABC, as this is the corresponding value in 2020. This is just an example of a large table i have in a database. Thank you

testfile

Upvotes: 1

Views: 101

Answers (1)

outon
outon

Reputation: 116

You just should think on what you need.

You need to update your table WHERE year is 2019.

UPDATE my_table e
...
WHERE year = 2019

You need to update field sales_type to the same value in 2020.

UPDATE my_table e
   SET sales_type = (...)
WHERE year = 2019

But how I get the same value that 2020:

SELECT sales_type
  FROM my_table i
 WHERE my_customername = i.customername AND year = 2020

But my_customername is the same that customername in external table.

So finally you got:

UPDATE my_table e
   SET sales_type =
         (SELECT sales_type
            FROM my_table i
           WHERE e.customername = i.customername AND year = 2020)
 WHERE year = 2019

I did try this, but get a syntax error. Please can you advise?

UPDATE SalesMaster_bkup e
   SET [Sales Typ] =
         (SELECT [Sales Typ]
            FROM SalesMaster_bkup i
           WHERE e.customername = i.customername AND year = 2020)
 WHERE year = 2019

Upvotes: 1

Related Questions