set data from a table in itself

Question

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 ?

Gordon Linoff · Accepted Answer · 2020-02-24T16:42:07.340

0

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;

edited Feb 24 '20 at 16:42

answered Feb 24 '20 at 16:40

Gordon Linoff

1,242,037
58
646
786

I have to keep the 2019 records. That's why i try to use Set, instead UPDATE. – JUAN GB Feb 24 '20 at 16:41

score 0 · Answer 2 · answered Feb 24 '20 at 16:51

If I'm reading this correctly, you want to create a duplicate set of rows in your table, and change the date for the newly created rows.

To do that, you can SELECT the rows you are interested in, but hard code the value you need to change.

INSERT INTO ldf.vin_EntePublicoLDF
(
  <List of all columns, with ejr_id listed last>
)
SELECT 
  <List of all columns EXCEPT ejr_id>,
  2020 AS ejr_id --<--- Hard-code your year column here.
FROM ldf.vin_EntePublicoLDF 
WHERE ejr_id = 2019;

I moved ejr_id to the end of both column lists to make it easy to find. You could, though, leave it in whatever order it appears in, and just be sure to substitute your hard coded value in the SELECT clause of the INSERT statement.

set data from a table in itself

2 Answers2