Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Correct mysql statement query

Posted on 2006-04-13
14
Medium Priority
?
159 Views
Last Modified: 2010-05-01
Im trying to write a sql execute where it will delete column1 from first table where it will match column 3 from the next table.
0
Comment
Question by:thenone
  • 5
  • 5
  • 4
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16451317
> it will delete column1
you mean update the column value to null or empty?

update yourtable
  set column1 = NULL
from yourtable t
join nexttable n
on n.column3 = t.column1
0
 
LVL 8

Author Comment

by:thenone
ID: 16451324
no basically i have a table with a column of records and what i want to do is delete those records that match table2 column 3 records.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16451362

delete yourtable
from yourtable t
join nexttable n
on n.column3 = t.column1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16451384
thenone,
DELETE table1.columnToDelete
FROM table1 INNER JOIN table2 ON table1.colMatch = table2.colMatch
WHERE (([table1].[colMatch]=[table2].[colMatch]));

Bob
0
 
LVL 8

Author Comment

by:thenone
ID: 16451391
it sais unkown table
0
 
LVL 8

Author Comment

by:thenone
ID: 16451400
what should i put in colMatch
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16451408
thenone,
I assume your are putting your table and field names in the corresponding placeholders?

Bob
0
 
LVL 8

Author Comment

by:thenone
ID: 16451412
WHERE (([table1].[colMatch]=[table2].[colMatch])); Yes but I get an error on this line
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16451419
thenone,
For the table1 reference, colMatch will be the name of the column that you want to match to a column in table2. The table2 reference to colMatch will be the name of the column that you want to match to the column in table1.
If you want to post the actual column names and table names maybe it would be easier to understand.

Bob
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16451427
thenone,
This is an example line........ the table1 should be replaced with your table name. the colMatch should be replaced with your column name, etc.
> WHERE (([table1].[colMatch]=[table2].[colMatch])); Yes but I get an error on this line


Bob
0
 
LVL 8

Author Comment

by:thenone
ID: 16451429
i did that and i get an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[business_submit].[url]=[business].[url]))' at line 1
0
 
LVL 12

Accepted Solution

by:
Bob Lamberson earned 2000 total points
ID: 16451473
thenone,
Sorry but this is T SQL not MySQL - I don't work with MySQL much so not sure if they are the same syntax.

Here's a ref to MySQL site, and some syntax you can work with
http://dev.mysql.com/doc/refman/5.0/en/delete.html
13.2.1. DELETE Syntax
Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

There is also some discussion on that page that may help.

Bob
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16451565
delete from [business_submit]
using [business_submit] t,  [business] n
where n.[url] = t.[url]
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16451569
and PLEASE, specify the database ( and version ) you are working with next next questions immediately, saves a lot of time
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question