CODEWITHSUNDEEP
sqlsql-server
q phan
q phan

Reputation: 99

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

Expected result:

Date1        Date2
----------------------
2020-09-01   Tuesday
2020-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)

Error

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

Upvotes: 0

Views: 44

Answers (2)

Thom A
Thom A

Reputation: 95949

Considering that the value of Date2 is based on the value of date1, then why not just use a calculated column instead?

ALTER TABLE dbo.[table] DROP COLUMN date2;
ALTER TABLE dbo.[table] ADD date2 AS DATENAME(WEEKDAY,date1);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270883

No subquery is needed:

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.

Upvotes: 1

Related Questions