Update and Delete command

14 Jan 2006 1:42 PM

Ali.M

Hi All
I have two tables that I want to can delete or update some records of them
with compairing with each other.I mean I want to chech if field1 from table1
is or isnot equal with field2 from table2 I can delete or update that records.
Let me know how can I do that.
Thanks for quick responses
Ali Mortazavi

14 Jan 2006 2:01 PM

ML

Difficult to say until you post DDL, but look up "full outer join" in Books
Online.

ML

---
http://milambda.blogspot.com/

14 Jan 2006 2:03 PM

Jens

Your explanation is a bit wacky. But just for some pseudo code your
solution could be something like this:

UPDATE SomeTable
    SET SomeColumn = 'SomeValue'
FROM SomeTable
INNER JOIN SomeOtherTable
   ON SomeTable.JoinedColumn = SomeOtherTable.JoinedColumn
Where SomeTable.CompareColumn != SomeOtherTable.CompareColumn

the same for Delete:

DELETE FROM SomeTable
INNER JOIN SomeOtherTable
   ON SomeTable.JoinedColumn = SomeOtherTable.JoinedColumn
Where SomeTable.CompareColumn != SomeOtherTable.CompareColumn

You need a column to koin to find the appropiate related rows in the
other table, then you can check two columns against each other.

HTH, Jens Suessmeyer.

15 Jan 2006 9:12 AM

Ali.M

Dear Jens
Hi,
I checked your code section delete but it doesn't work on my data base. my
data base is access and has some tables, I want to can delete some tables
from one of them with check that is there this serial in another one or not.
Table 1: (sanad)
Snd_id: Autonumber
Sho_snd: Text
Serial: Text
Table 2: (sabt)
Id: Autonumber
Regnumber: Number
Originalbarcode: Text
I want to can delete rows from table2 where originalbarcode equal table1
serial field.
I'm waiting for your response.
Thanks and regards

Show quote

"Jens" wrote:

> Your explanation is a bit wacky. But just for some pseudo code your
> solution could be something like this:
>
> UPDATE SomeTable
>     SET SomeColumn = 'SomeValue'
> FROM SomeTable
> INNER JOIN SomeOtherTable
>    ON SomeTable.JoinedColumn = SomeOtherTable.JoinedColumn
> Where SomeTable.CompareColumn != SomeOtherTable.CompareColumn
>
> the same for Delete:
>
> DELETE FROM SomeTable
> INNER JOIN SomeOtherTable
>    ON SomeTable.JoinedColumn = SomeOtherTable.JoinedColumn
> Where SomeTable.CompareColumn != SomeOtherTable.CompareColumn
>
>
> You need a column to koin to find the appropiate related rows in the
> other table, then you can check two columns against each other.
>
> HTH, Jens Suessmeyer.
>
>

15 Jan 2006 6:37 PM

Jens

You are in the SQL Server newsgroups not the Access ones, SQL syntax is
sometimes different from this in SQL Server. Perhaps you try to
followup the thread in the access groups.

But If I interpret your question right, you want to delete rows from
table two, which attribute of originalbarcode exists in the table1
stored in the serial field.

Therefore an appropiate solution would be (don´t know if the syntax if
applicable in Access, this is years ago ;-) ):

DELETE
FROM table 2
WHERE
originalbarcode IN
(
SELECT serial FROM table1
)

HTH, Jens Suessmeyer.

14 Jan 2006 8:30 PM

Louis Davidson

I would strongly suggest you put together a minimal example of what you want
to do. This really isn't very clear at all.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Ali.M" <A***@discussions.microsoft.com> wrote in message
news:3DB8A1A4-D93A-43D6-BC49-E09F6A22DA9E@microsoft.com...
> Hi All
> I have two tables that I want to can delete or update some records of them
> with compairing with each other.I mean I want to chech if field1 from
> table1
> is or isnot equal with field2 from table2 I can delete or update that
> records.
> Let me know how can I do that.
> Thanks for quick responses
> Ali Mortazavi