|
View previous topic :: View next topic
|
| Author |
Message |
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
Hi!
How to handle -811 during update?
| Code: |
UPDATE <Table1 t1>
SET <t1.Col1> = (SELECT <t2.Col1>
FROM <Table2 t2>
WHERE t2.Col1 != 'ERT'); |
I have made the SET clause as 'equal to' predicate.
I'm getting more than one value from sub query.
I have to update two rows in table 1 with the two different values from table2.
How can I do it? |
|
| Back to top |
|
 |
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| HameedAli wrote: |
| I have to update two rows in table 1 with the two different values from table2. |
How do you differentiate between which row gets which value? |
|
| Back to top |
|
|
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
By applying values for the key columns of table 1, I differentiate which row got which value.
I perform a join on table 2 to table 1 to understand the values. |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| HameedAli wrote: |
By applying values for the key columns of table 1, I differentiate which row got which value.
I perform a join on table 2 to table 1 to understand the values. |
Then apply those same values on table 1.
Maybe a WHERE for t1 in addition to the WHERE for t2? |
|
| Back to top |
|
|
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
| Code: |
UPDATE <Table1 t1>, <Table2 t21>
SET <t1.Col1> = (SELECT <t2.Col1>
FROM <Table2 t2>
WHERE t2.Col1 != 'ERT')
where <t21.Col2> in (123,321,111); |
two tables in update clause isn't possible, right? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Code: |
UPDATE <Table1>
SET Col1 = (SELECT Col1
FROM Table2
WHERE Col1 NOT = 'ERT')
AND Col2 in (123,321,111); |
you were receiving -811 because you original WHERE clause in the subselect
returned more than one row.
and NO, you can not UPDATE multiple tables in a single SQL
if the additional WHERE condition of COL2 reduces your result set to 1 row,
then the above code will work.
in the future, suggest that you - not use != and instead use NOT =
- determine what WHERE conditions only return one row by SPUFIing a simple select based on your subquery
- when it is not required do not use table and column qualification
you are aware that you will be setting all rows in TABLE1??
and last but not least 'UPDATING MULTIPLE VALUES'
is really a poor Topic subject. |
|
| Back to top |
|
|
|
|
