complex update query

  • Thread starter Thread starter Southern at Heart
  • Start date Start date
S

Southern at Heart

I can't figure out the syntax for my query. Here's what I need it to do:
tb1 & tb2 are joined by both having the field [Key Code]

If tb1.field1 = "my text" then I want to move (not just copy) the string in
tb2.field1 to tb1.field1
....in other words, I want to delete the contents of tb2.field1 and also
replace it to tb1.field1

Is this possible?
thanks.
 
Southern said:
I can't figure out the syntax for my query. Here's what I need it to
do: tb1 & tb2 are joined by both having the field [Key Code]

If tb1.field1 = "my text" then I want to move (not just copy) the
string in tb2.field1 to tb1.field1
...in other words, I want to delete the contents of tb2.field1 and
also replace it to tb1.field1

Is this possible?
thanks.
Click to expand...

Not with a single query no.
 
Make sure to back up your database first. There is a possible problem in
that if tbl1.field1 is set to zero-lenght string first then tbl2.field1 will
be set to that value of zero-lenght string. In my limited experience the SQL
is processed from last to first but I would not bet much on it --
so BACKUP BACKUP BACKUP
UPDATE [tbl1] INNER JOIN [tbl2] ON [tbl1].[field1] = [tbl2].[field1] SET
[tbl1].[field1] = "", [tbl2].[field1] = [field1]
WHERE ((([tbl1].[field1])="my text"));
 
Back
Top