View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = ’22-MAR-2007′
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = ‘Roberts’ AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A.
It would not execute because two tables cannot be used in a single UPDATE statement.
B.
It would not execute because the SELECT statement cannot be used in place of the table name.
C.
It would execute and restrict modifications to only the columns specified in the SELECT statement.
D.
It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
is C is correct?
create table mytab1
( name varchar(10));
create table mytab2
( id int,
name varchar(10));
insert into mytab1 values(‘karthi’);
insert into mytab2 values(1,’karthi’);
update ( select name from mytab )
set name=’janani’
where name=(select name from mytab2 where name=’karthi’);
I have tried like this…
But saying error is
Every derived table must have its own alias
mytab not exists.
c is correct
I think B
@kar,option c is correct
create table mytab1
( name varchar(10));
create table mytab2
( id int,
name varchar(10));
insert into mytab1 values(‘karthi’);
insert into mytab2 values(1,’karthi’);
update (select name from mytab1)
set name=’janani’
where name=(select name from mytab2 where name=’karthi’);
–1 row updated–
I do not see the added value of restricting modifications to the columns in the select statement rather than the name of the table. In all cases only the column specified after the SET instruction will be updated:
UPDATE sales
SET time_id = ’22-MAR-2007′
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = ‘Roberts’ AND
credit_limit = 600);
Agreed
credit_limit cust_credit_limit !
if we assume that the question has no errors then all the anwers would be wrong !
C