0

Am using SQL server 2012 and am looking for conversion of Oracle statement having substitution variable:

    UPDATE customers
    SET region = '&region'
    WHERE state = '&state'

Someone please help me on this.

Improve this question

2 Answers 2

Reset to default
1

I am by no means an Oracle expert but I think that substition variables are specific to SQL*PLUS and not the Oracle database.

SQL*PLUS

define region=West
define state=California

 UPDATE customers
    SET region = '&region'
    WHERE state = '&state'

My understanding is that SQL*PLUS will execute the following SQL:

 UPDATE customers
    SET region = 'West'
    WHERE state = 'California'

The equivalent in MS SQL Server 2012 would be:

Declare @Region varchar(100)
Declare @State varchar(100)

Set @Region = 'West'
Set @State = 'California'

Update Customers
Set region = @Region
Where State = @State

Or you could lose the variables all together and simply use:

Update Customers
Set Region = 'West'
Where State = 'California'

EDIT:

Update only affect data that is currently in the table. If you add more data to the table at a later date you would need to run the update again.

e.g.

Adding more rows with a State of "California" will not automatically set the Region column to "West" for the inserted rows.

Also ensure that you are using the same data types (and lengths) for your variables that you have declared in your table structure.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the query, it really helped me! Also, I just wanted to know, does executing the above statement mean in future whenever I insert value as "California" in state, by default puts the value "west" in region OR it just sets value only to the present customer table?
Am getting "String or binary data would be truncated. The statement has been terminated." even after assigning variables
@PrincessS regarding your first comment, see my edit. Your second comment; you need to make sure that the length of your variables (and their respective values) match the data types of your table structure.
It perfectly works for me now. Just that i was using single quote in Update statement. Also, Thanks for the clarification provided.
1

It will be something like 

Declare @State varchar(100)
Declare @Region varchar(100)

UPDATE customers
SET region = @Region
WHERE state = @State
Improve this answer

6 Comments

You need to also assign values to your variables otherwise they will be NULL
@xionutz2k: Am getting "String or binary data would be truncated. The statement has been terminated." message after executing this
The error indicates that the length of the @Region variable that we have defined exceeds the length of the region column in the customers table. I have randomly chosen 100 as the length when defining the variable, but you should check what's the length of the table column first. Try to use DESC customers to obtain the description of the table first (columns and their data types)
@xionutz2k: I have assigned the same datatype and length for both state and region as it is originally defined for Customers table.
the other possible cause would be that you try to initialize the @Region variable with a value that exceeds its defined length
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.