SQL insert ID from another table by comparing data from both tables

INSERT, REPLACE, etc have very precise meanings in SQL because they are commands. In future please use the correct terminology when asking a question.

In this case I will presume you want to substitute ARTIST_ID for ARTIST_NAME; normally this would be an UPDATE. However, using the wrong column (and wrong data type) is bad practice, which will confuse you and any of your co-workers. So you need to change the column name on the target table. There are various ways to acheieve this goal; the one I outline below is the most forgiving on mistakes and exceptions.

So, let's make a foreign key reference between table 1 (artists) and table 2 (songs). The following example may contain more steps than you require, in which case omit the things you don't want to do.

alter table songs add artist_id number
/

-- use MERGE to update one table with values from another

merge into songs song
using (select * from artists) artt
on (artt.artist_name = song.artist_name)
when matched then
  update song.artist_id = artt.artist_id
/

-- this step will fail if you have values of ARTIST_NAME in SONGS 
-- which don't match anything in ARTISTS 

alter table songs 
  modify artist_id not null
/

-- if the last step succeeded, normalise the data model

alter table songs
  drop column artist_name
/

Additionally you should add a foreign key (assuming ARTISTS has ARTIST_ID as a primary key...

alter table songs
  add constraint song_artt_fk foreign key (artist_id) references artists
/