SQL update a secondary table

I need to update the fields in "survey_data" from the values in "bld1".
Both tables have a field called "SEQ", "NOR","SOU", etc.

I tried the following in MySql

UPDATE survey_data
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES
FROM bld1  
INNER JOIN bld1 on survey_data.SEQ  = bld1.SEQ

Open in new window


I get this error:
#1064 - 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 'FROM bld1  
INNER JOIN bld1 on survey_data.SEQ  = bld1.SEQ' at line 7

Open in new window


What did I miss?
breeze351Asked:
Who is Participating?
 
Russell FoxConnect With a Mentor Database DeveloperCommented:
Make sure you have indexes on the SEQ key in both tables, like below. You shouldn't be getting timeouts for data sets that size.
CREATE INDEX IX_bld1_SEQ ON bld1(SEQ);
GO
CREATE INDEX IX_survey_data_SEQ ON survey_data(SEQ);
GO

Open in new window

1
 
Russell FoxDatabase DeveloperCommented:
You're not mentioning survey_data in the join. Try this:
UPDATE survey_data
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES
FROM bld1
	INNER JOIN survey_data
		ON survey_data.SEQ  = bld1.SEQ

Open in new window

1
 
Dustin SaundersDirector of OperationsCommented:
Points to Russell, but I would also make a note for your future SQL stuff that you can always alias the table names to cut down on the fingerwork you have to do.  Here's a simple example:
SELECT c.Name, o.Number
FROM [Customer] c
INNER JOIN [Orders] o ON o.CusID = c.CusID

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
breeze351Author Commented:
Russel
No Joy!!!
I copied your code:
UPDATE survey_data
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES
FROM bld1
	INNER JOIN survey_data
		ON survey_data.SEQ  = bld1.SEQ

Open in new window


Ran it and got the following error:
#1064 - 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 'FROM bld1
    INNER JOIN survey_data
        ON survey_data.SEQ  = bld1.SEQ' at line 7

Open in new window


Line 7 is "FROM bld1".

The code looks good to me.
0
 
Dustin SaundersDirector of OperationsCommented:
I overlooked it was for MySQL rather than SQL Server.  Syntax is slightly different.

http://www.mysqltutorial.org/mysql-update-join/

Should look something like this:
UPDATE survey_data
INNER JOIN bld1 ON bld1.SEQ = survey_data.SEQ
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES

Open in new window


Don't have MySQL handy to verify, but should correct.  As with anything, when you're testing be sure to back up your db.
0
 
breeze351Author Commented:
When I tried to run this, the server timed out.  There are about 21,000 in bld1 and only about 5.000 in survey data.

What would happen if I changed the code to read:
UPDATE survey_data
[u]INNER JOIN survey_data ON  survey_data.SEQ = bld1.SEQ[/u]
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
This would result in a logic error and a SQL error being that bld is not defined in the query
INNER JOIN survey_data ON  survey_data.SEQ = bld1.SEQ

Open in new window

the server timed out
What tool are you using to run the query?
I suggest that you add more time to the script execution max time.

You could also rig a WHERE clause to update subsets of records, but this is if for some reason you couldn't avoid timeout.
0
 
Ryan ChongCommented:
When I tried to run this, the server timed out.  There are about 21,000 in bld1 and only about 5.000 in survey data.

What would happen if I changed the code to read:

UPDATE survey_data
INNER JOIN survey_data ON  survey_data.SEQ = bld1.SEQ
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES
as mentioned by NerdsOfTech, to be more explicit if you couldn't spot the error:

UPDATE survey_data
INNER JOIN survey_data ON  survey_data.SEQ = bld1.SEQ

it should be something mentioned by Dustin in ID: 42440626

UPDATE survey_data
INNER JOIN bld1 ON bld1.SEQ = survey_data.SEQ
...

Open in new window


server timed out could due to the issue above.
0
 
breeze351Author Commented:
If I use the code to join bld1 on survey_data:
INNER JOIN bld1 ON bld1.SEQ = survey_data.SEQ

Open in new window

The server times out.   There are more records in bld1 then in space_survey.  Why can't I do it the other way?
0
 
Ryan ChongCommented:
If I use the code to join bld1 on survey_data:

INNER JOIN bld1 ON bld1.SEQ = survey_data.SEQ

The server times out

Server times out would be another issue then the syntax error...
we need to know how the data looks like...

is these 2 tables (bld1 and survey_data) only related with one another by field: SEQ ?
0
 
breeze351Author Commented:
Yes.  The only relation ship is on the field "SEQ".  "SEQ" is the key for the building, this relates to "SEQ" in "survey_data.  Each "survey_data" record has a field called "SEQ" and then an ID field for the individual piece of space.

I didn't set this up.  I'm trying to fix old code.
0
 
Ryan ChongCommented:
so you could have inherited with some existing old codes and old database structure.

what you can do is try to review that existing codes and db structure, and when necessary do some codes optimization or/and db normalization.

we can't access to your db for verification but at least your initial asked issue (syntax error) has been resolved.
0
 
breeze351Author Commented:
Russell
I created the indexes and the query did run.  My only question is the query returned 56 records updated.
Is this because the data in bld1 equaled the data in survey_data except for these 56 records?
0
 
NerdsOfTechTechnology ScientistCommented:
My only question is the query returned 56 records updated.
Is this because the data in bld1 equaled the data in survey_data except for these 56 records?

INNER JOIN requires that SEQ from both tables match, when they do, update sets :
 survey_data.NOR = bld1.NOR,
 survey_data.SOU = bld1.SOU,
 survey_data.EAS = bld1.EAS,
 survey_data.WES = bld1.WES

You might have an issue with the parameter in the ON issue...
If there is more than one record from both tables that equal the same SEQ, it runs again.

s.SEQ, s.NOR
1, 10
2, 20
3, 30
4, 40

b.SEQ. b.NOR
1, 10
1, 20
2, 30
1, 40

INNER JOIN ON b.SEQ = s.SEQ:
1,10|1,10
1,10|1,20
1,10|1,40
2,20|2,30

4 comparisons, but only 2 rows affected.

result s:
s.SEQ, s.NOR
1, 40
2, 30
3, 30
4, 40

So the question is (to see if you are affected by this issue):
is SEQ unique for both tables?
0
 
breeze351Author Commented:
Index fixed it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.