0

I have a following table

CREATE TABLE LoadRecords(RoomNum INTEGER DEFAULT 0, UserNum INTEGER DEFAULT 0, PresetNum INTEGER DEFAULT 0, PresetName TEXT DEFAUTL 'Preset', Load1 INTEGER DEFAULT 0, Load2 INTEGER DEFAULT 0, Load3 INTEGER DEFAULT 0, Load4 INTEGER DEFAULT 0, Load5 INTEGER DEFAULT 0, Load6 INTEGER DEFAULT 0, Load7 INTEGER DEFAULT 0, Load8 INTEGER DEFAULT 0, Load9 INTEGER DEFAULT 0, Load10 INTEGER DEFAULT 0, Load11 INTEGER DEFAULT 0, Load12 INTEGER DEFAULT 0, Load13 INTEGER DEFAULT 0, Load14 INTEGER DEFAULT 0, Load15 INTEGER DEFAULT 0, Load16 INTEGER DEFAULT 0, Load17 INTEGER DEFAULT 0, Load18 INTEGER DEFAULT 0, Load19 INTEGER DEFAULT 0, Load20 INTEGER DEFAULT 0, Circuit1 TEXT DEFAUTL '-', Circuit2 TEXT DEFAUTL '-', Circuit3 TEXT DEFAUTL '-', Circuit4 TEXT DEFAUTL '-', Circuit5 TEXT DEFAUTL '-', Circuit6 TEXT DEFAUTL '-', Circuit7 TEXT DEFAUTL '-', Circuit8 TEXT DEFAUTL '-', Circuit9 TEXT DEFAUTL '-', Circuit10 TEXT DEFAUTL '-', Circuit11 TEXT DEFAUTL '-', Circuit12 TEXT DEFAUTL '-', Circuit13 TEXT DEFAUTL '-', Circuit14 TEXT DEFAUTL '-', Circuit15 TEXT DEFAUTL '-', Circuit16 TEXT DEFAUTL '-', Circuit17 TEXT DEFAUTL '-', Circuit18 TEXT DEFAUTL '-', Circuit19 TEXT DEFAUTL '-', Circuit20 TEXT DEFAUTL '-')

I would like to update all Load1, Load2, Load3... Load20 from one row to few other rows within the same RoomNum but different UserNum inside of the same table.

enter image description here

is there a query that can do that for specific PresetNum ?

Edit. This is what I have used. I wish SQLite supported loops as I really need to be able to change RoomNum plenty of times...

enter image description here

1 Answer 1

1

For MySQL (the question is MySQL-tagged)

UPDATE table t1, table t2
SET t1.field1=t2.field1, t1.field2 = t2.field2, ...
WHERE
      /* copy one record                     */
/*    t1.id = 123 AND t2.id = 456                       */
      /* copy a bunch of consecutive records */
/*    t1.id BETWEEN 111 AND 123 AND t2.id - t1.id = 321 */

For SQLite (the question is SQLite-tagged too)

UPDATE table
SET field1 = ( SELECT field1 
               FROM table t 
               WHERE t.id = 123 )
   /* or       WHERE t.id - table.id = 123     */
WHERE id = 456
/* or      WHERE id BETWEEN 444 AND 456        */
9
  • it's all within the same table Jun 22, 2020 at 12:36
  • @ArkadiuszRycyk You're right.
    – Akina
    Jun 22, 2020 at 12:39
  • then I don't understand how I should use your solution update LoadRecords set ????????????????? where RoomNum = 59 and PresetNum = 1; Jun 22, 2020 at 12:42
  • @ArkadiuszRycyk Where you looking at??? update LoadRecords t1, LoadRecords t2 SET ...
    – Akina
    Jun 22, 2020 at 12:48
  • I have no idea how to apply your solution to my problem. Jun 22, 2020 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.