CODEWITHSUNDEEP
sqljoinsassql-update
Hena
Hena

Reputation: 81

Update column with specific values ( if clause)

I am trying to insert new values into specific column with "update table set specific column...." I have two tables like this:

Table1

Name       Idnumber          Score 
JOHN         DB              10
JOHN         IT              NULL
KAL          DB              9
HENRY        KK              7
KAL          DB              10
HENRY        IP              9
ALI          IG              10
ALI          PA              9

Table2

NAME    |  MONEY |
-----------------|
JOHN    |        | 
-----------------|
KAL     |        |
-----------------|
HENRY   |        |
-----------------|
ALI     |        |
-----------------

And I want that my table look like this:

Updated Table

NAME    |  MONEY |
-----------------|
JOHN    |        | 
-----------------|
KAL     |   yes  |
-----------------|
HENRY   |   half |
-----------------|
ALI     |   yes  |
-----------------

The condition for writing "yes" into money column is that all scores under same name in table1 should be 9 or higher, the condition for writing "half" is that scores should be no lower than 6 ( what I mean is that scores might be 10,9,8,7 and 6)

So basically it means, that, for example, Henry cannot be selected and I cannot write "yes" next to his name in updated table, because he has score under the value of 9 in one line , but in the other he has the score of 7.(null values also should be emitted).

I'm using a sas program. Thank you!!!

Upvotes: 1

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You seem to be treated NULL as a value that is less than "9". In standard SQL (which works in both MySQL and SAS, the original tags) is:

update table2 t2
    set money = 'YES'
    where not exists (select 1 
                      from table1 t1
                      where t1.name = t2.name and coalesce(t1.score, 0) < 9
                     );

If you want to guarantee that there is at least one row in table2, one method is aggregation:

update table2 t2
    set money = 'YES'
    where (select (case when sum(case when t1.score > 9 then 1 else 0 end) = count(*)
                        then count(*)
                   end)
           from table1 t1
           where t1.name = t2.name
          ) > 0

Upvotes: 1

GMB
GMB

Reputation: 222402

You could try the following syntax:

update table2 
set money = 'YES'
where not exists (select 1 from table1 t1 where t1.name = table2.name and t1.score < 9)

However this would also update records that have no match in table1. In MySQL, one simple option to avoid that uses a join:

update table2 t2
inner join (select name, min(score) min_score from table1 group by name) t1
    on t1.name = t.name and t1.min_score >= 9
set t2.money = 'YES'

Upvotes: 0

Related Questions