-1

This is my code

UPDATE ks_tidy SET announcedWeek = (
SELECT week(dateAnnounced) as weekAnnounced 
FROM ks_tidy ) WHERE announcedWeek = ''

and the problem I'm running into is it doesn't work when I'm trying to update more than 1 row?

1242 - Subquery returns more than 1 row

My table has two columns: dateAnnounced (DATE[yyyy-mm-dd], UNIQUE, PRIMARY KEY), weekAnnounced (INT). The weekAnnounced column was added to store weekAnnounced, which I am trying to "calculate" using the WEEK() SQL function. I have thousands of rows of data and this table gets updated regularly so manually doing this is not an option.

Community
  • 1
  • 1
keymistress
  • 138
  • 9

1 Answers1

0

I don't see why you think you need a sub query for this

UPDATE ks_tidy SET announcedWeek = week(dateAnnounced) 
WHERE announcedWeek = ''

If this is not the case please add sample data and expected output as text to the question. AND should you be testing for null rather than ''? AND do you really need to store an easily derived value?

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • I've updated my question with more info. I don't really need to test for anything and the WHERE clause is optional. I would like to GROUP BY week instead and visualising data using Observable and vega-lite which doesn't support the week property at the moment. – keymistress May 01 '20 at 13:56