Search code examples
sqlsetmysql-error-1193

set data from a table in itself

So, i have to migrate some data, the query is:

SELECT * FROM ldf.vin_EntePublicoLDF WHERE ejr_id = 2019;

and i have to take those data but set "2020" I try with:

SET vin_EntePublicoLDF.ejr_id = 2020
INSERT INTO ldf.vin_EntePublicoLDF
SELECT * FROM ldf.vin_EntePublicoLDF WHERE vin_EntePublicoLDF.ejr_id = 2019;`

But i recive an error:

[Err] 1193 - Unknown system variable 'ejr_id'

Any suggestion ?

Solution

  • If you are trying to update the data, use update:

    update ldf.vin_EntePublicoLDF 
    set ejr_id = 2020
    where ejr_id = 2019;

    If you want to insert new rows:

    INSERT INTO ldf.vin_EntePublicoLDF ( . . ., ejr_id )  -- list columns here
    SELECT . . ., 2020
    FROM ldf.vin_EntePublicoLDF
    WHERE ejr_id = 2019;