CODEWITHSUNDEEP
shelloracle11g
Angelina
Angelina

Reputation: 2265

Check if value within column exists, if not create the column

I need to check if one of the columns in my db contains specific value. If it doesn't I want to create that row with folowing values:

#!/bin/bash
#
MODEL=$1

if true (SELECT * FROM table.STATISTICS
         WHERE MODEL = '$MODEL' )

do this (INSERT INTO table.STATISTICS('$MODEL',0,SYSDATE,0,SYSDATE,0); )

Upvotes: 0

Views: 193

Answers (2)

Angelina
Angelina

Reputation: 2265

get_count () {
    sqlplus -s username/pass <<!
    set heading off
    set feedback off
    set pages 0
    select count(model) from statistics
    where model='$MODEL';
!
}

count=$(get_count $1)

if [ "${count:-0}" -eq 0 ]; then
    echo "its zero"

    sqlplus -S username/pass << EOF
    whenever sqlerror exit 1;
    set echo on
    set verify off
        INSERT INTO table.STATISTICS VALUES('$MODEL',0,SYSDATE,0,SYSDATE,0); 
    exit;
    EOF

fi

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

You could use a merge for this, run through SQL*Plus as a 'heredoc', so you don't have to do a separate count operation; the merge will do that for you effectively:

#!/bin/bash

MODEL=$1

sqlplus -s /nolog <<!EOF

connect user/pass

merge into statistics s
using (select '${MODEL}' as model, 0 as num1, sysdate as date1,
  0 as num2, sysdate as date2 from dual) t
on (s.model = t.model)
when not matched then
insert (s.model, s.num1, s.date1, s.num2, s.date2)
values (t.model, t.num1, t.date1, t.num2, t.date2);

!EOF

But using your real column names, obviously. It's better to list them explicitly even for a plain insert.

Upvotes: 2

Related Questions