-1

I am stuck and can use a pointer or two. I have data in multiple tables in Schema A There may (or may not) be additional fields in an update (not important for the moment). I want to copy the data in Schema A into Schema B, but change the prefix on every table.

I can create Schema B with the data structure in Schema A and change the table prefixes using CREATE TABLE ... SELECT * from That works The sticking point is UPDATE. How do I update the information in Schema B with the data from Schema C ?
A and C are identical, C has the more recent information. My question: 100 tables, how to do a batch update of all records using the information in Schema C ? There are no keys

Thanks. Constructive suggestions cheerfully accepted.

CREATE TABLE gets me part way there. Need Update Table

nbk
  • 45,398
  • 8
  • 30
  • 47
DaveRiba
  • 1

1 Answers1

1

It is basically the same as any other UPDATE, only you add the schema before the tables and join them

UPDATE B.table1 t1
JOIN C.t2 t2 ON t1.id = t2.id
SET t1.mycolumn = t2.mycolumn, t1.mycolumn2 = t2.mycolumn2
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
nbk
  • 45,398
  • 8
  • 30
  • 47
  • An important point to consider if there is a replication. The schema selected should be the one in which the update will take place, because in statement format (I'm not sure for ROW or MIX ) it will break the replication. In your example OP should run `use schemaB;` before running the update – Ergest Basha Aug 05 '23 at 19:40
  • no the schema with table name is unique on thee server, so there is no need to use `USE` – nbk Aug 05 '23 at 19:43
  • So far so good, except ... Is there a way to make this generic for a batch update? SET t1.mycolumn = t2.mycolumn, t1.mycolumn2 = t2.mycolumn2 Can this be SET t1.* = t2.* or ????? – DaveRiba Aug 05 '23 at 19:52
  • 1
    No you have to write every column down, or you build it from information schema see https://stackoverflow.com/a/15532584/5193536 – nbk Aug 05 '23 at 19:55