CODEWITHSUNDEEP
sqlsas
Snackbreak
Snackbreak

Reputation: 17

SQL - alter table and add new values

I am trying to add a new column to an existing table, then add in the values for that column. I don't want to run my code and wreck the existing table if it's wrong. I would just like to check whether there are any errors I am missing, since I am fairly new to using sql:

PROC SQL;
ALTER TABLE work.tablename
ADD  
CPdate INT NOT NULL;

UPDATE work.tablename
SELECT t1.CP
FROM WORK.CPtable t1

INNER JOIN work.tablename t2
ON t1.spec=t2.spec AND t1.code = t2.code

;QUIT;

Upvotes: 0

Views: 2070

Answers (1)

Tom
Tom

Reputation: 51566

The simple answer is don't do that. Make a new table with the data you want. 

data new_table ;
  merge tablename (in=in1 ) CPTABLE (keep=spec code cp rename=(cp=cpdate));
  by spec code ;
  if in1 ;
run;

If you really want to stick with SQL then use a create table statement.

PROC SQL;
  create table new_table as 
    select a.*,b.cp as CPdate
    from tablename a 
    left join CPtable b
    on a.spec=b.spec AND a.code = b.code
  ;
QUIT;

You can ask SAS to use the same name for the output table and it will only replace it if the code runs without errors, but that is only for errors that cause invalid code and NOT programmer errors in logic that cause the wrong data to get created.

If you no longer need the old version you can delete it.

proc delete data=tablename ;
run;

Upvotes: 1

Related Questions