I need to recover some data in one column from a backup table and write it to the relevent row in wo-CodePudding

My table has a notes column (Text not Null) and I just noticed that all notes older than a month or so are missing. I have a month old backup with most of those Notes.

I have a column called PO which will be the unique identifier for each Row.

I'll call the backup DB Source and the working DB Target.

So, I want to copy the column Notes from Source into Target where Target.PO = Source.PO but only when Source.Notes <> ""

The following code throws a syntax error even though an online validator OKs it.

UPDATE
    Target,
    Source
SET
    Target.Notes = Source.Notes
WHERE
    Target.PO = Source.PO
    AND Source.Notes <> '""';

CodePudding user response：

You can only UPDATE a single table, you are specifying 2 tables.

You could use:-

UPDATE target 
    SET notes = (SELECT notes FROM source WHERE source.po = target.po) 
    WHERE target.po IN (SELECT po FROM source) 
    AND target.notes = ''
    AND (SELECT source.notes FROM source WHERE source.po = target.po) <> ''
;

This does an additional check using AND target.notes = '' which could be omitted to exactly be the requested solution.

Edit re comment

I totally screwed up my descripion of the tables and the source code submitted. I have 2 databases Target and Source. They both have a table called Sales , I want to copy the column Notes from Source.sales to Target.sales if source.sales.notes <> "" and where source.sales.po = target.sales.po

Then what you do you is open/connect the Target database, then ATTACH the Source database (using ATTACH <the filename for the Source Database> AS source).

Then you could use (untested):-

UPDATE main.sales AS ms 
    SET notes = (SELECT notes FROM source.sales AS ss  WHERE ss.po = ms.po) 
    WHERE ms.po IN (SELECT po FROM source.sales) 
    AND ms.notes = ''
    AND (SELECT notes FROM source.sales WHERE po = ms.po) <> ''
;