Convert Access query into MySQL - Two tables with no Join
-
Hi this is my first post and request for help with SQL which I am fairly new to.
I need to convert some queries from Access to SQL before but need help with one.
I have an Access query that updates a field in one table where other fields match values in another able table but the two tables are not joined in anyway.
The lookup table contains an ID field and some fields with various set times and I want to specify that the table to be updated is updated where all of these values match.
Whilst this works in Access perfectly I don't know how to use the Lookup table that contains the values when it doesn't join to the table that needs to be updated.
Below is the SQL that I have copied from the Access query (which just for information only has the two tables linked via an ODBC connection to the tables on an SQL server hence the table names start dbo.)
Please can someone help me convert the Access query into SQL?
UPDATE dbo_lt_pOperator, dbo_lt_LookupPreSetShifts SET dbo_lt_pOperator.ShiftID = "2"
WHERE (((dbo_lt_pOperator.amShiftStart)=[Set_amShiftStart]) AND ((dbo_lt_pOperator.amShiftEnd)=[Set_amShiftEnd]) AND ((dbo_lt_pOperator.pmShiftStart)=[Set_pmShiftStart]) AND ((dbo_lt_pOperator.pmShiftEnd)=[Set_pmShiftEnd]) AND ((dbo_lt_pOperator.Fri_amShiftStart)=[Set_Fri_amShiftStart]) AND ((dbo_lt_pOperator.Fri_amShiftEnd)=[Set_Fri_amShiftEnd]) AND ((dbo_lt_pOperator.Fri_pmShiftStart)=[Set_Fri_pmShiftStart]) AND ((dbo_lt_pOperator.Fri_pmShiftEnd)=[Set_Fri_pmShiftEnd]) AND ((dbo_lt_pOperator.Active)="Y") AND ((dbo_lt_LookupPreSetShifts.ID)=1));
The table dbo_lt_pOperator and field ShiftID is the one I want to update with the value '2'
the table dbo_lt_LookupPreSetShifts contains the values I want to use but where the ID = 1 and all of the shift time fields match the shift time fields in the lt_pOperator table.
I have also uploaded a screenshot of the Access query in design mode so its easier to see visually what I am trying to achieve in SQL.
I really hope someone can help a damsel in distress as I have been scouring the internet trying to find out how to write the SQL. Obviously a JOIN statement wont work, or at least it didnt when I tried to write it.
Thanks for looking 🙂
-
Hi there.....and welcome
just to be sure....your question title mentions "MySQL"...are you using MySQL or MS SQL?
for further posts
my suggestion is that you read this article please
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and then re post with some sample set up scripts....will save any confusion for you and us.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Would help if you posted your code using the IFCode Shortcuts and formatted your code.
Original code reformatted:
UPDATE dbo_lt_pOperator, dbo_lt_LookupPreSetShifts SET
dbo_lt_pOperator.ShiftID = "2"
WHERE
(((dbo_lt_pOperator.amShiftStart) = [Set_amShiftStart]) AND
((dbo_lt_pOperator.amShiftEnd) = [Set_amShiftEnd]) AND
((dbo_lt_pOperator.pmShiftStart) = [Set_pmShiftStart]) AND
((dbo_lt_pOperator.pmShiftEnd) = [Set_pmShiftEnd]) AND
((dbo_lt_pOperator.Fri_amShiftStart) = [Set_Fri_amShiftStart]) AND
((dbo_lt_pOperator.Fri_amShiftEnd) = [Set_Fri_amShiftEnd]) AND
((dbo_lt_pOperator.Fri_pmShiftStart) = [Set_Fri_pmShiftStart]) AND
((dbo_lt_pOperator.Fri_pmShiftEnd) = [Set_Fri_pmShiftEnd]) AND
((dbo_lt_pOperator.Active) = "Y") AND
((dbo_lt_LookupPreSetShifts.ID) = 1));
Rewritten for MS SQL Server (not sure if this would work in MySQL):
UPDATE po SET
ShiftID = "2"
FROM
dbo_lt_pOperator po
INNER JOIN dbo_lt_LookupPreSetShifts ps
ON (po.amShiftStart = ps.[Set_amShiftStart] AND
po.amShiftEnd = ps.[Set_amShiftEnd] AND
po.pmShiftStart = ps.[Set_pmShiftStart] AND
po.pmShiftEnd = ps.[Set_pmShiftEnd] AND
po.Fri_amShiftStart = ps.[Set_Fri_amShiftStart] AND
po.Fri_amShiftEnd = ps.[Set_Fri_amShiftEnd] AND
po.Fri_pmShiftStart = ps.[Set_Fri_pmShiftStart] AND
po.Fri_pmShiftEnd = ps.[Set_Fri_pmShiftEnd])
WHERE
po.Active = "Y" AND
ps.ID = 1;
-
Apologies, as I said this is all new to me. Im using SQL Server 2008 if that helps
i will read your suggest document and revert accordingly.
Thanks for you reply.
-
I dont know what IFCode is sorry.
I am using SQL server 2008 so assume that is MS SQL and not MYSQL as per my title apologies as I said I am new to all of this and this.
-
Caz68 (5/12/2016)
I dont know what IFCode is sorry.I am using SQL server 2008 so assume that is MS SQL and not MYSQL as per my title apologies as I said I am new to all of this and this.
Edited my code above, please look at the change.
Edit:
IFCode Shortcuts are shown to the left of the edit window when you are writing a post.
Also, you will see them if you use QUOTE to include a post in your reply.
-
Caz68 (5/12/2016)
I dont know what IFCode is sorry.I am using SQL server 2008 so assume that is MS SQL and not MYSQL as per my title apologies as I said I am new to all of this and this.
When typing a post you will see these on the left of the edit window...
Using these shortcuts will allow you format your text to do stuff like turn this:
SELECT TOP 5 Col1, Col2, Col3 FROM SomeTable WHERE Col3 = 'Stuff'
Into this:
SELECT TOP 5 Col1, Col2, Col3 FROM SomeTable WHERE Col3 = 'Stuff'Just highlight the text you want to format and then click on the desired shortcut. It will automatically wrap it with the right markup.
Much easier to read especially when the code can be verbose! 😉
Cheers,
SELECT quote FROM brain WHERE original = 1
0 rows returned -
yb751 (5/12/2016)
Caz68 (5/12/2016)
I dont know what IFCode is sorry.I am using SQL server 2008 so assume that is MS SQL and not MYSQL as per my title apologies as I said I am new to all of this and this.
When typing a post you will see these on the left of the edit window...
Using these shortcuts will allow you format your text to do stuff like turn this:
SELECT TOP 5 Col1, Col2, Col3 FROM SomeTable WHERE Col3 = 'Stuff'
Into this:
SELECT TOP 5 Col1, Col2, Col3 FROM SomeTable WHERE Col3 = 'Stuff'Just highlight the text you want to format and then click on the desired shortcut. It will automatically wrap it with the right markup.
Much easier to read especially when the code can be verbose! 😉
Cheers,
nice explanatory post yb +100
oh...and dont forget to use "preview" tab before you post
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply