CODEWITHSUNDEEP
sqlsas
Ladenkov Vladislav
Ladenkov Vladislav

Reputation: 1297

How to modify rows, which are present in other table

Suppose, i have two tables: A and B

Table A:

    aaa     79506333388
    bbb     79502068252
    ccc     79139859806
    ddd     79113515443
    eee     79231128300
    fff     79194631152

Table B:

79231128300
79194631152

I want to modify 1st column of A if values in 2nd columns are present in table B:

    aaa             79506333388
    bbb             79502068252
    ccc             79139859806
    ddd             79113515443
    MODIFIED        79231128300
    MODIFIED        79194631152

I expected, i could implement something like:

data A;
    set A;
    if 2nd_col in B then 1st_col = 'MODIFIED';
run;

or using proc sql:

proc sql;
    create table A as
    select *,
    (case when 2nd_col in B then 1st_col = 'MODIFIED' else 1st_col) as 1st_col_1
    from A;
    delete 1st_col;
run;

But niether of them didn't work. Please, don't try to simplify the task, i need to do exactly what i ask: modify the rows, which are present in other table.

Upvotes: 0

Views: 52

Answers (2)

Longfish
Longfish

Reputation: 7602

Here's a simple solution using proc sql in SAS.

data A;
input A1 $ A2;
datalines;
aaa     79506333388
bbb     79502068252
ccc     79139859806
ddd     79113515443
eee     79231128300
fff     79194631152
;
run;

data B;
input B1;
datalines;
79231128300
79194631152
;
run;

proc sql;
update A
set A1 = 'MODIFIED'
where exists (select B1 from B where A.A2=B.B1);
quit;

Upvotes: 1

Gaurav Rajput
Gaurav Rajput

Reputation: 647

Begin specific to your question posted by you, solution to your problem is as given below.

DECLARE @TABLE1 TABLE(COLUMN1 VARCHAR(50), COLUMN2 VARCHAR(50))
INSERT INTO @TABLE1 VALUES('aaa','79506333388')
INSERT INTO @TABLE1 VALUES('bbb','79502068252')
INSERT INTO @TABLE1 VALUES('ccc','79139859806')
INSERT INTO @TABLE1 VALUES('ddd','79113515443')
INSERT INTO @TABLE1 VALUES('eee','79231128300')
INSERT INTO @TABLE1 VALUES('fff','79194631152')
DECLARE @TABLE2 TABLE(COLUMN1 VARCHAR(50))
INSERT INTO @TABLE2 VALUES('79231128300')
INSERT INTO @TABLE2 VALUES('79194631152')
UPDATE T1 SET COLUMN1='MODIFIED' FROM @TABLE1 T1 JOIN @TABLE2 T2 ON > T1.COLUMN2=T2.COLUMN1
SELECT * FROM @TABLE1

output

------------------------
COLUMN1 |   COLUMN2
------------------------
aaa     |   79506333388
bbb     |   79502068252
ccc     |   79139859806
ddd     |   79113515443
MODIFIED|   79231128300
MODIFIED|   79194631152
------------------------

Upvotes: 0

Related Questions