CODEWITHSUNDEEP
sqloracle11g
gvar
gvar

Reputation: 39

I am attempting to decipher this SQL query I am a bit confused

SELECT 'delete schema.'||table_name||' where code =''XYZ'';'||'
commit;' FROM users

Here is what I am seeing

select delete scheme.table_name where code = 'xyz'; commit; from users

Thanks!

Upvotes: 0

Views: 62

Answers (2)

philipxy
philipxy

Reputation: 15118

SELECT c FROM t returns a table with one column named c with a row for each row of table t. An output row contains its input row's column c value.

SELECT 'pq' FROM t returns a table with one column and one row. The value for the column is the string formed by a character p then a character q.

SELECT 'p''q' FROM t returns a table with one column and one row. The value for the column is the string formed by a character p then an apostrophe character ' then a character q.

SELECT 'p'||c||'q' from t returns a table with one column with a row for every row of table t. An output row contains a string that is formed by a character p then the characters that form the string representation of its input row's column c value then a character q.

SELECT 'delete schema.'||table_name||' where code =''XYZ'';'||'commit;' FROM users

So if you run this query when table users has two rows and the two column table_name values are the strings formed by characters ab and by cd then the result is a table with with one column and two rows. The values for the one column and two rows are the strings formed by the following characters:

delete schema.ab where code ='XYZ';commit;
delete schema.cd where code ='XYZ';commit;

If there is only one row in table user and you run your query as a subselect in another query then the one-column one-row table returned by the subselect can be (implicitly) converted to the string that is its one element.

You probably want to pass the (string) value of one such row to the DBMS as a sequence of SQL commands.

But if you are "seeing" the string formed by the characters

 select delete scheme.table_name where code = 'xyz'; commit; from users

then you are doing something else with your query string than running it as a query. But you haven't clearly said what string you are submitting to what system in what state to get what output.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

I can see the confusion. But the result should be simple

You arent executing a delete statment you are creating a string

You are using table user and there is a field called table_name

But your final result is wrong

that is ~ equivalent to

select 'delete scheme.table_name where code = 'xyz'; commit;' 
from users

What ever the value on table_name was

Maybe after the query, you copy/paste the result to your rdbms to execute some commands.

Upvotes: 2

Related Questions