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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Russell FoxDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.