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.

Princess S
  • 89
  • 1
  • 1
  • 9

2 Answers2

1

It will be something like 

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

UPDATE customers
SET region = @Region
WHERE state = @State
xionutz2k
  • 754
  • 3
  • 9
  • 1
    You need to also assign values to your variables otherwise they will be `NULL` – codingbadger Jul 11 '13 at 14:33
  • @xionutz2k: Am getting "String or binary data would be truncated. The statement has been terminated." message after executing this – Princess S Jul 11 '13 at 14:51
  • 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 Jul 11 '13 at 14:58
  • @xionutz2k: I have assigned the same datatype and length for both state and region as it is originally defined for Customers table. – Princess S Jul 11 '13 at 15:03
  • the other possible cause would be that you try to initialize the @Region variable with a value that exceeds its defined length – xionutz2k Jul 11 '13 at 15:11
  • @xionutz2k: Now it work for me, everything was fine except I was using single quotes in update statement. – Princess S Jul 11 '13 at 15:21
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.

codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • 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? – Princess S Jul 11 '13 at 14:49
  • Am getting "String or binary data would be truncated. The statement has been terminated." even after assigning variables – Princess S Jul 11 '13 at 14:57
  • @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. – codingbadger Jul 11 '13 at 15:10
  • It perfectly works for me now. Just that i was using single quote in Update statement. Also, Thanks for the clarification provided. – Princess S Jul 11 '13 at 15:19