I'm trying to update column in this table: Expected result: And this is my update query - I get an error and I'm not sure where the issue comes from. Thank you. Error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used

SQL update with more than 2 records

I’m trying to update column in this table:

Date1        Date2
-------------------
2020-09-01   NULL
2020-09-02   NULL

​x
 
Date1        Date2-------------------2020-09-01   NULL2020-09-02   NULL​

Expected result:

Date1        Date2
----------------------
2020-09-01   Tuesday
2020-09-02   Wednesday

 
Date1        Date2----------------------2020-09-01   Tuesday2020-09-02   Wednesday​

And this is my update query – I get an error and I’m not sure where the issue comes from. Thank you.

UPDATE table
SET Date2 = (select DATENAME(WEEKDAY, date1) from table)

 
UPDATE tableSET Date2 = (select DATENAME(WEEKDAY, date1) from table)​

Error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer

No subquery is needed:

UPDATE table
    SET Date2 = DATENAME(WEEKDAY, date1);

 
UPDATE table    SET Date2 = DATENAME(WEEKDAY, date1);​

That said, storing such redundant information is usually not a good idea. You can just call the function.

Advertisement

Answer