I am trying to update values in a column (in a live table). However, this depends on the values generated from another table as well.
when I do:

select *
from Table(s) with  left outer join
where condition
I get the specified records.

However, when I try to update the column fields using
UPDATE LiveTable
SET column = 'string value'
where exists
(
--same block of select statement as above i.e.
select *
from Table(s) with left outer join
where condition
)
I get all records in the live tables (which I don't want)

I'm sorry if this sounds like a trivial problem, but I am stuck and any help is greatly appreciated.
Thanks in advance!!!

Join the Live table with the table that you're extracting the value from. Don't use the exists clause. Hope that is what you're trying to accomplish.

UPDATE (xxx) WHERE (condition) SET (xxx) = (xxx)

no need for exists.

EXISTS returns a boolean value. So if conditions is true it result in ALL or if condition fails it result in NONE. So if you want to udpate selected rows only then dont use EXISTS at all.

I am not 100% sure what you want but you can try this:

UPDATE LiveTable
SET column = 'string value'
where livetable.columname in (select tablename.columname from tablename(S) left outer join where conditions).

 

 

plz ask rawbee. he is SQL guru.

suman anwar suman

Be more precise... But what arnzombie has given it should work for you

That's what You looking about?????

 

Well, here is the problem in detail SINCE I GOT SO MANY RESPONSE (overwhelmed by all your help
)

I have a live TABLE A

I created a TABLE B with same #, datatype of columns as TABLE A . TABLE B filters the data from TABLE A by looking at column Exp.Date = 2 months from now, with other where conditions.

In addition, TABLE B has 10 more columns.

I inserted same data into TABLE B as TABLE A and they all columns from TABLE A as original TABLE A column name

Example:
Insert into TABLE B
select

TABLEA alias. Column1 as Column1

TABLEA alias. Column2 as Column 2

And so on…

 (Since TABLEB  has 10 additional columns, I joined TABLE A with 2 other tables . i.e.
append to the above select INSERT INTO TABLE B
SELECT
10 new columns as 10 names)

TABLE1.columnA as xyz,

TABLE2.columnB as abc….etc (10 instances)

FROM
TABLE A
left outer join TABLE1
left outer join TABLE 2

WHERE
conditions

-----------------------------------------------------------------------------------------

Now,

A stored Procedure (1^st Notice) needs to run every Monday.

 Let’s say I created a batch job starting June 2^nd, it needs to show data(satisfying the where conditions) from (Aug 2^nd to Aug 8^th) i.e. 2 months from the date the report is run.

 
Then, a column(Status) in TABLE B needs to be updated  to value = ‘1stNotice’ (originally, all Status = NULL)

 Simultaneously, all these new records generated from TableB by running the stored procedure need to be inserted(appended) to Table A(the live table).

Also, in the live table, the column(STATUS) needs to be updated to value = ‘1^st notice’(originally, all Status = NULL)

 THIS PROCESS FOR FIRST NOTICE GOES ON FOR A MONTH.

 
Then 30 days later, same cycle needs to be repeated to update TABLE B Status = ‘2^nd Notice’ where Status = ‘1^st Notice’

Now, STATUS in TableA also needs to be updated to ‘2^nd Notice’ (we do not insert- insertion only happens once to change the Status from NULL to 1^st Notice)….

 THIS PROCESS FOR 2^nd NOTICE YET AGAIN GOES ON FOR A MONTH…..finally, same procedures for final notice

 

PROBLEMS:

1. How do I specify Date between (1^st day of 30days from now to 7^th day of 30days from now)?
2. UPDATE – I tried using inner joins and the suggestions provided to me. However it does not seem to work.

 

 

I do not know about SQL but I am guessing oracle and SQL should be very similar why don't you use sysdate +30 and sysdate +37 to get the 1st and the 7th day after 30 days

 

SQL> select sysdate from dual;

SYSDATE
----------
05/29/2008

SQL> select sysdate+30 from dual;

SYSDATE+30
----------
06/28/2008

 

Then to update you should be able to update it using straingt fwd update..

Update TableA set status='2nd Notice' where staus='1st Notice'

Localboy- right on about the date Thanks!! My mind seems to be blurry lately LOL

I did this like you suggested and it works wonders:)

select...
from...
where
dateColumn between DATEADD(dd,30,getdate()) and DATEADD(dd,37,getdate())
...

All I have a problem now is with UPDATE

I modifed this code trying to use joins - doesnt work...any ideas?

UPDATE LiveTableA
SET Status = '1st Notice'
where exists
(
select
a.ID,
a.Status,
----
-----
a.ExpDate as ExpDate,
Table1.name as User,
------
-----
----
Table2.city as Address,

 
from
LiveTableA a
left outer join ..Table1..on
left outer join ..Table2..on

where
 ExpDate between DATEADD(dd,30,getdate()) and DATEADD(dd,37,getdate())
-------
and Table1.codeColumn in ( 'ABC', 'XYZ')
)

THANKS IN ADVANCE!

if you are updating table1 status value with respect to the report why are you using exists and joins..

Let me see if I get it right

 

table 1

column 1

values 1 2 3 4 5 6    status column = null

Assuming your where ExpDate pulls column 1 2 5 6

if you want just to update the status to 1st invoice then you can do

update table1 set status='1st invoice' where status=null and ExpDate between DATEADD(dd,30,getdate()) and DATEADD(dd,37,getdate())

 

I used your EXPDATE which is sql server code I guess

 

But the where condition is insufficient. UPDATE LiveTableA takes into consideration Table1 and Table2


from
LiveTableA a--------------------------------------------1.
left outer join ..Table1..on-------------------------------2.
left outer join ..Table2..on--------------------------------3.

where
 a.ExpDate between DATEADD(dd,30,getdate()) and DATEADD(dd,37,getdate()) ------Table A (1)condition
-------
and Table1.codeColumn in ( 'ABC', 'XYZ') ----------------------Table1 (2) condition

)

can you post the structure of tab1 and 2

along with tabA

I can only do that later this evening. Thanks for all your help!

I am still not sure what result you are expecting. But Again dont use EXISTS for it does not FILTER anything.

Maybe this can help a little bit:

-------------------------------------------------------------------------------------------

STEP 1 to insert into TABLE_B

INSERT INTO TABLE_B (

SELECT .... FROM TABLE_A

LEFT OUTER JOIN TBL1

LEFT OUTER JOIN TBL2

a.ExpDate between dateadd(mm,2,getdate()) AND dateadd(mm,7,getdate())

-------------------------------------------------------------------------------------------

STEP 2:

UPDATE TABLE_A --- OR TABLE_B as needed

SET status='1st Notice'

WHERE table_a.id in (SELECT .... FROM TABLEA,Tbl1,Tbl2  WHERE......)

AND a.ExpDate >= dateadd(mm,2,getdate())

AND a.ExpDate<=dateadd(mm,2,getdate())+7

AND status in NULL ; -- OR '1st Status 'as required.

--------------------------------------------------------------------------------------------------

Also Note that if you are using procedure i suggest you to us IF..ELSE clause.

sorry syntax is in oracle standard

IF status = NULL THEN

......update table_a set status='1st Notice'..........

ELSIF status ='1st Notice' THEN

...........update table_a set status='2nd Notice'..........

End IF;

------------------------------------------------------------------------------------------------------------

I hope this would be helpful for you.

If not dont stress try to forget this for a while...RELAX....And get back with fresh mind you will get this done easily. GOOD LUCK

Thanks arnzombie- I'll try that.

To simpe to learn SQL. check this web site: www.w3schools.com

It is easy way to learn SQL (SQL Tutorial)