SQL Syntax 1

[NotSQL]

Hi

Im trying to find out if a record exists in one table and not the other then update a field with a status code. My tables look like this:

Table 1 Table 2
RecNum RecNumb
ABC IDat
Vcode PN
Stat

So if the record exists in table 2 i want to set the stat to I.

Would i use If Exists?

 

[hmckillop]

update tb1
set stat = 'I'
from table1 tb1
inner join table2 tb2 on tb2.recnumb = tb1.recnum

this should do the job

"I'm living so far beyond my income that we may almost be said to be living apart

 

[NotSQL]

Thanks,

That works if it exists but it was meant to say doesnt exists..

 

[r937]

UPDATE Table1
   SET Stat = 'I'
  FROM Table1 AS t1
LEFT OUTER
  JOIN Table2 AS t2
    ON t2.RecNumb = t1.RecNum
 WHERE t2.RecNumb IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[NotSQL]

Thanks

I thought about using this syntax but its seems to just hang. Can you tell me what might be wrong with it or is there a better way of writing it.

Update table1
set Stat = case when table1.RecNumb
= table2.RecNumb
and table2.IBATCH = 'R' then 'R' Else 'I' end
From table1, table2

 

[r937]

it hangs because you wrote a cross join

From table1, table2

this returns all possible combinations


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[hmckillop]

try

update tb1
set stat = 'I'
from table1 tb1
where not exists
(select 'X' FROM table2 tb2
where tb2.recnumb = tb1.recnum)



"I'm living so far beyond my income that we may almost be said to be living apart