to replace and to create 1

[adnil]

our client is having a 'spring cleaning' on their database now and would like the following done:

1) table name: assessment
field name: ABC
end date: dd/MM/yyyy
if field name = ABC and end date is greater than 1/12/2008, to replace all the records' end date to 30/11/2008 and

2) to create another record with a field name of XYZ with a start date of 1/12/2008

how can i combine all the queries into 1 script? any suggestions please?

thank you.

 

[SQLSister]

I suggest you identify the records you want to change with a selct stament and insert them into a table variable.

THen write the update statment joining to the table variable.
Then write the insert statment using the select clause and selcting from the table variable.

"NOTHING is more important in a database than integrity." ESquared

 

[RDCushing]

Would something like this work for you?

select *
into #temp
from assessment

update assessment
set ABC = '11/30/2008'
where ABC > '12/1/2008'

insert into a
( XYZ
, <OtherField>
, <OtherField>
)
select '12/1/2008'
, <OtherData>
, <OtherData>
from #Temp t
[inner/right outer] join assessment a
on t.<OtherData> = a.<OtherData>




Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP

 

[adnil]

hi richard

thank you for your advise.

following your script, i have:
1) created a table
2) update the end date for ABC
3) tried inserting the data and this is where i am getting an error message - ORA-01400: cannot insert NULL into "assessment"."record_id". record_id is a auto generated number. how can i overcome this?

thank you very much for your assistance.

cheers

 

[RDCushing]

You say that assessment.record_id is "auto generated", but if it is failing when trying to insert NULL, it clearly is not auto-generated by SQL (as an Identity column would be). How is [record_id] auto-generated?

Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP

 

[SQLSister]

BTW that is an Oracle error. You would be better servered in an Oracle forum. This is the formum for Microsoft SQL Server and the SQL code you write for this product does differ often significantly from Oracle code.

"NOTHING is more important in a database than integrity." ESquared

 

[adnil]

hi richard

the record_id is a mandatory field and is automatically generated/populated by the system when we add a row of data. is there anyway i can bypass this NULL thing and just insert data for 2 fields in the table?

cheers
lin

 

[RDCushing]

Well, I don't know about Oracle. However, in SQL Server, if a row has an IDENTITY column, you just omit that column from your INSERT statement and SQL auto-populates the column for you.

Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP